Good Way to Build Insert

cmnorton
2010-03-03
2012-09-19
  • cmnorton
    cmnorton
    2010-03-03

    I believe I am building my insert string wrong. I am able to select multiple
    fields from multiple tables. The data is good; I've verified it. But MySQL is
    basically forcing me to do two things: the first is to encapsulate each field
    in single quotes; the second is to put str() around most of the fields due to
    concatenating long and str errors.

    What is the best way to build an entire insert statement and its data?

    Thanks.

     
  • Andy Dustman
    Andy Dustman
    2010-03-03

    I believe you are. There is no need to quote anything. Show us your code. Also
    read PEP-249.

     
  • cmnorton
    cmnorton
    2010-03-03

    I am asking these questions, because I am suspecting that my query data is
    built wrong. Some rows insert just fine, but others do not, and I cannot see
    on inspection why a particular row fails. This problem is why I've been asking
    about building the correct syntax, and so on.

    def createNewCsRowSQL(incoming_data):

    db_date = getMySQLDateTime()

    lnam_fnam_l = parseLnamFnam(incoming_data)

    if incoming_data == 0:

    active_state = 'I'

    else:

    active_state = 'A'

    sql_cmd = \

    "INSERT INTO customer_synch " + \

    "( " + \

    "Action, EndpointId, DeviceType, ChannelNumber, Status, MeterID, " + \

    "AcctID, PremiseID, CustomerID, CustomerLastName, " + \

    "CustomerFirstName, CompanyName, PremiseType, AddressLine1, " + \

    "AddressLine2, City, State, Zip, Latitude, Longitude, Comments, " + \

    "MLOG, DecodeTypeOverride, Route, last_update) " + \

    "VALUES ( " + \

    "'A'" + "," + \

    "'" + str(incoming_data) + "'" + "," + \

    "'" + str(incoming_data) + "'" + "," + \

    "'" + str(incoming_data) + "'" + "," + \

    "'" + active_state + "'" + "," + \

    "'" + str(incoming_data) + "'" + "," + \

    "'" + str(incoming_data) + "'" + "," + \

    "'" + str(incoming_data) + "'" + "," + \

    "'" + str(incoming_data) + "'" + "," + \

    "'" + escSQStr(lnam_fnam_l) + "'" + "," + \

    "'" + escSQStr(lnam_fnam_l) + "'" + "," + \

    "'" + str(incoming_data) + "'" + "," + \

    "'" + escSQStr(incoming_data) + "'" + "," + \

    "'" + str(incoming_data) + "'" + "," + \

    "'" + escSQStr(incoming_data) + "'" + "," + \

    "'" + str(incoming_data) + "'" + "," + \

    "'" + str(incoming_data) + "'" +"," + \

    "'" + str(incoming_data) + "'" +"," + \

    "'" + str(incoming_data) + "'" + "," + \

    str(incoming_data) + "," + \

    "'" + "" + "'" + "," + \

    "'" + str(0) + "'" + "," + \

    "'" + str(0) + "'" + "," + \

    "'" + str(incoming_data) + "'" + "," + \

    "'" + db_date + "'" + ");"

    return sql_cmd

    def compareIncomingData():

    .

    .

    .

    Open a database connection.

    incoming_rc = incoming_cur.fetchone()

    cmp_sql_cmd = createCsLookupSQL(incoming_rc)

    outgoing_cur = db.cursor()

    outgoing_cur.execute(cmp_sql_cmd)

    outgoing_rc = outgoing_cur.fetchone()

    if None == outgoing_rc:

    insert_sql = createNewCsRowSQL(incoming_rc)

    print(insert_sql)

    rc = outgoing_cur.execute(insert_sql)

    else:

    .

    .

    .

     
  • Andy Dustman
    Andy Dustman
    2010-03-03

    Wow, that looks painful.

    First, you should use triple-quoted strings, which can span multiple lines: h
    ttp://python.org/doc/1.4/tut/node70.html

    Second, you should use %s as a parameter placeholder, and then just pass your
    parameters as a tuple as the second argument to cursor.execute(). Don't worry
    about type conversion or quoting because that's done automatically.

    After you do this, you will have something like this:

    sql_cmd = \
    """INSERT INTO customer_synch 
    ( 
    Action, EndpointId, DeviceType, ChannelNumber, Status, MeterID,
    AcctID, PremiseID, CustomerID, CustomerLastName, 
    CustomerFirstName, CompanyName, PremiseType, AddressLine1, 
    AddressLine2, City, State, Zip, Latitude, Longitude, Comments, 
    MLOG, DecodeTypeOverride, Route, last_update) 
    VALUES (%s, %s, %s, ... %s)
    """
    
     
  • cmnorton
    cmnorton
    2010-03-03

    Thank you very much. That's why I asked the separate question about syntax,
    which -- thank you -- you've answered. I will go try your suggestion tomorrow
    and report back. Thank you again. You're right that my way is painful, so this
    should fix a lot of that.

     
  • cmnorton
    cmnorton
    2010-03-04

    In the words of the late Jerome Howard, "Success!" And thank you.

    def createNewCsRowSQL(incoming_data):

    """

    Basically, the way to use Python and its MySQLDb module is to build an insert
    statement

    without the data, using place holders. Then construct the data in a tuple.

    """

    sql_cmd = \

    """INSERT INTO customer_synch

    (

    Action, EndpointId, DeviceType, ChannelNumber, Status,

    MeterID, AcctID, PremiseID, CustomerID, CustomerLastName,

    CustomerFirstName, CompanyName, PremiseType, AddressLine1, AddressLine2,

    City, State, Zip, Latitude, Longitude,

    Comments, MLOG, DecodeTypeOverride, Route, last_update

    )

    VALUES

    (

    %s, %s, %s, %s, %s,

    %s, %s, %s, %s, %s,

    %s, %s, %s, %s, %s,

    %s, %s, %s, %s, %s,

    %s, %s, %s, %s, %s

    )

    """

    db_date = getMySQLDateTime()

    lnam_fnam_l = parseLnamFnam(incoming_data)

    if incoming_data == 0:

    active_state = 'I'

    else:

    active_state = 'A'

    sql_data = ('A', incoming_data, incoming_data, incoming_data, active_state, \

    incoming_data, incoming_data, incoming_data, incoming_data, lnam_fnam_l, \

    lnam_fnam_l, incoming_data, incoming_data, incoming_data, incoming_data, \

    incoming_data, incoming_data, incoming_data, incoming_data, incoming_data, \

    'No comments', 0, 0, incoming_data, db_date)

    sql_info =

    return sql_info

     
  • Andy Dustman
    Andy Dustman
    2010-03-04

    I am kind of curious about what getMySQLDateTime() is doing. You can just use
    Python datetime objects, i.e.

    from datetime import datetime
    db_date = datetime.now()
    
     
  • cmnorton
    cmnorton
    2010-03-04

    def getMySQLDateTime():

    now = datetime.today()

    db_date = now.strftime("%Y-%m-%d %H:%M:%S")

    return db_date

     
  • Lloyd Kvam
    Lloyd Kvam
    2010-03-04

    Good catch Mr. Dustman.

    You can simply put datetime.today()

    into your tuple of parameters. There is no need to first convert it to a
    string.

    Also you do not need the backslash () characters to continue lines within
    parentheses.

     
  • cmnorton
    cmnorton
    2010-03-04

    Will make the change. Thank you.