The World of Mayukh Bose

<< Back to Main Page Mayukh's world: e-mail me | about
Mayukh's World: Python Tutorials: ADO and Python Thursday, November 23, 2017
Python and ADO
  • 0. Introduction
  • 1. What is ADO
  • 2. Basic Objects
  • 3. Using Parameters
  • 4. ADO/Python Basics
  • 5. Connection Object
  • 6. RecordSet Object
  • 7. Command Object
  • 8. More Command Fun
  • 9. Command and Procs
  • 10. Creating Params
  • 11. Links and Credits
  • My Free Software
  • Delphi/C++ Builder
  • Pocket PC
  • FreeSpeech Chat
  • C/C++ Freebies
  • Perl
  • Python
  • Ruby
  • My Python Code
  • ADO and Python
  • Addlice
  • IE Controller
  • 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.


    <<Previous: The ADO Command Object ^Up to Mayukh's World^ Next: Command Objects and Stored Procedures >>

    Copyright © 2004 Mayukh Bose. All rights reserved.
    This work may be freely reproduced provided this copyright notice is preserved.
    OPTIONAL: Please consider linking to this website (http://www.mayukhbose.com/) as well.