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.

     

Get latest updates about Open Source Projects, Conferences and News.

Sign up for the SourceForge newsletter:





No, thanks