Good Way to Build Insert

[cmnorton]

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]

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

[Andy Dustman]

Also take a look at http://mysql-python.sourceforge.net/MySQLdb.html#some-
examples

[cmnorton]

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]

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]

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]

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]

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]

def getMySQLDateTime():

now = datetime.today()

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

return db_date

[Lloyd Kvam]

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]

Will make the change. Thank you.