| My Free Software | |||||||
|
| My Python Code | |||
|
| Python Tutorials and Software | e-mail me |
|
|
|
Chapter 8: More on the Command Object
| In the previous chapter, we saw how to use a Command object to perform select queries on a database. We also saw how it was possible to pass parameters to the query and optimize it. In this chapter, we will explore techniques to insert, update and delete data using queries. First we will deal with the quick (albeit dirty and naive) way to insert data into a database. We will write a small program that allows us to input a person's first name, last name, age and income into a table. We will assume that the first name and last name fields are of type varchar, age is an integer and income is a numeric(6, 2) field. In this example, we will use the Execute method of the Connection object to add new rows to the database. The example assumes a MySQL database engine, but it could easily be modified for just about any database engine (Access, SQL Server, Oracle, PostGres etc.) by merely changing the ConnectionString. The rest of the code remains the same for any database engine. |
Quick-N-Dirty Insert Query
from win32com.client import Dispatch
from adoconstants import *
# Create Connection object and connect to database.
oConn = Dispatch('ADODB.Connection')
oConn.ConnectionString = "Driver={MySQL ODBC 3.51 Driver};" + \
"Server=192.168.0.50;Port=3306;" + \
"User=foobar;Password=bigsecret;Database=mytestdb"
oConn.Open()
# Now ask the user for input
while 1:
# Ask the user for input
fname = raw_input("Enter First Name: ")
if (fname == ""):
break
lname = raw_input("Enter Last Name: ")
age = raw_input("Enter your age: ")
income = raw_input("Enter your income: ")
# Now prepare the SQL statement to do the data insertion.
sql = "INSERT INTO person(first_name, last_name, age, income) " + \
"VALUES ('" + fname + "', '" + lname + "', " + age + ", " + income + ")"
# Now execute the SQL statement that we prepared above.
oConn.Execute(sql)
# Close up and clean up
oConn.Close()
oConn = None
|
At first, the method seems fairly straightforward. The program asks the user for input, then
prepares an appropriate SQL statement and executes it. However on closer observation, a few
flaws come to light. For one thing, we are rebuilding the SQL statement each time, even though
the only thing that has changed are the values of the fields. The second problem is much more
serious. If the user were to enter, say, "O'Bannon" for the last name, then the sql statement
that we prepared would read something like this: sql = "INSERT INTO person(first_name, last_name, age, income) VALUES ('Roy', 'O'Bannon', 21, 1250.25)" which is clearly the wrong thing, since the apostrophe will cause problems if you try to execute the statement. You'll need to replace a single apostrophe character with a doubled one ('') to make it process correctly. Now let's see how this same job can be done using a Command object and Parameter objects. |
Better Insert Query
from win32com.client import Dispatch
from adoconstants import *
# Create DB and connect to database.
oConn = Dispatch('ADODB.Connection')
oConn.ConnectionString = "Driver={MySQL ODBC 3.51 Driver};" + \
"Server=192.168.0.50;Port=3306;" + \
"User=foobar;Password=bigsecret;Database=mytestdb"
oConn.Open()
# Now create a command object and prepare the query
oCmd = Dispatch('ADODB.Command')
oCmd.ActiveConnection = oConn
# Create a query that accepts four parameters
oCmd.CommandType = adCmdText
oCmd.CommandText = "INSERT INTO person(first_name, last_name, age, income) " + \
"VALUES (?, ?, ?, ?)"
# Now create the Parameter objects
oParam1 = oCmd.CreateParameter('first_name', adVarChar, adParamInput, 50)
oParam2 = oCmd.CreateParameter('last_name', adVarChar, adParamInput, 50)
oParam3 = oCmd.CreateParameter('name', adInteger, adParamInput)
oParam4 = oCmd.CreateParameter('income', adNumeric, adParamInput)
oParam4.Precision = 6
oParam4.NumericScale = 2
oCmd.Parameters.Append(oParam1)
oCmd.Parameters.Append(oParam2)
oCmd.Parameters.Append(oParam3)
oCmd.Parameters.Append(oParam4)
# Request the query to be prepared ahead of time
oCmd.Prepared = True
while 1:
# Ask the user for input
fname = raw_input("Enter First Name: ")
if (fname == ""):
break
lname = raw_input("Enter Last Name: ")
age = raw_input("Enter your age: ")
income = raw_input("Enter your income: ")
# Set the Parameter values and execute our query
oParam1.Value = fname
oParam2.Value = lname
oParam3.Value = age
oParam4.Value = income
oCmd.Execute()
# Close and clean up
oCmd = None
oConn.Close()
oConn = None
|
In the above code, we create a Command object and assign it a SQL query which contains four
parameters. We then create four Parameter objects to correspond to the four variables in the
SQL statement. Then we prepare the statement, so that the database server knows ahead of time
that we intend to execute this statement multiple times. Then we request the user for input,
set the appropriate parameter values and execute the statement. This is more efficient because
we are not rebuilding the SQL statement each time we get a new set of values. Also, since we're
using Parameter objects, the code automatically escapes any apostrophe characters and
other special characters by itself. Hence an input of "O'Bannon" will be accepted and
processed correctly. Thus we can get around the two biggest issues of the Quick-N-Dirty method.
Note that in the above code, we created parameters of type adVarChar, adInteger and adNumeric. If you look carefully, you'll notice that CreateParameter() was called with different numbers of arguments depending on the parameter type and also that some parameter types need additional properties to be set. Chapter 10 details how to create different parameter types. You can also use the same techniques to handle UPDATE and DELETE statements using parameters. For instance, you can use a CommandText like this to update a row: oCmd.CommandText = "UPDATE person SET income = income * 1.5 WHERE age > ? AND age < ?" or delete rows like this: oCmd.CommandText = "DELETE FROM person WHERE age > ?" In the next section we will explore how to call stored procedures with parameters. |