Newbie needs Help

L

len

Hi all

I am writing a python program that inserts records into a database on
XP using mxODBC.

I need to write a section of code which will create the following SQL
command as an example;

INSERT INTO statecode (state, name) VALUES ('IL', 'Illinois')

This statement will be built up using the following code;

import mx.ODBC
import mx.ODBC.Windows
def insertFromDict(table, dict):
"""Take dictionary object dict and produce sql for
inserting it into the named table"""
sql = 'INSERT INTO ' + table
sql += ' ('
sql += ', '.join(dict)
sql += ') VALUES ('
sql += ', '.join(map(dictValuePad, dict)) # ??? this code does
NOT format correctly
sql += ')'
return sql

def dictValuePad(key): # ??? this code
does Not format correctly
return "'" + str(key) + "'"

db = mx.ODBC.Windows.DriverConnect('dsn=UICPS Test')
c = db.cursor()
insert_dict = {'state':'IL', 'name':'Illinois'}
sql = insertFromDict("statecode", insert_dict)
print sql
c.execute(sql)

I copied this code off of ASP and I sure it worked for his particular
circumstance but I need to format up the VALUE clause just a bit
different.

I will be working from a dictionary which will be continualy update in
another part of the program and this code is working.

Len Sumnler
 
J

John Machin

len said:
Hi all

I am writing a python program that inserts records into a database on
XP using mxODBC.

I need to write a section of code which will create the following SQL
command as an example;

INSERT INTO statecode (state, name) VALUES ('IL', 'Illinois')

This statement will be built up using the following code;

import mx.ODBC
import mx.ODBC.Windows
def insertFromDict(table, dict):
"""Take dictionary object dict and produce sql for
inserting it into the named table"""
sql = 'INSERT INTO ' + table
sql += ' ('
sql += ', '.join(dict)
sql += ') VALUES ('
sql += ', '.join(map(dictValuePad, dict)) # ??? this code does
NOT format correctly
sql += ')'
return sql

def dictValuePad(key): # ??? this code
does Not format correctly
return "'" + str(key) + "'"

db = mx.ODBC.Windows.DriverConnect('dsn=UICPS Test')
c = db.cursor()
insert_dict = {'state':'IL', 'name':'Illinois'}
sql = insertFromDict("statecode", insert_dict)
print sql
c.execute(sql)
Click to expand...

The code below will do what you say that you want to do -- so long as
all your columns are strings (varchar or whatever in SQL terms).
Otherwise IMHO you would be much better off doing it this way:
sql = "insert into policy (type, premium) values(?, ?)"
data = ('building', 123.45)
cursor.execute(sql, data)
for two reasons:
(1) let the ODBC kit worry about formatting dates, strings with
embedded single quotes, etc
(2) it can be more efficient; the sql is constant and needs to be
parsed only once
(3) [bonus extra reason] the way you are doing it is vulnerable to
what's called an "SQL injection attack"; although you have no doubt
eyeballed all the data, doing it that way is a bad habit to get into.

You should be able to modify the supplied code very easily to produce
the sql variety with "?" in it.

HTH,
John

C:\junk>type sqlinsdict.py
def sqlquote(astring):
return "'" + astring.replace("'", "''") + "'"

def insertFromDict(table, adict):
"""Take dictionary object dict and produce sql for
inserting it into the named table.
Sample input:
insert_dict = {'state':'IL', 'name':'Illinois'}
sql = insertFromDict("statecode", insert_dict)
Required output:
INSERT INTO statecode (state, name) VALUES ('IL', 'Illinois')
"""

t = [
'INSERT INTO ',
table,
' (',
', '.join(adict.keys()),
') VALUES (',
', '.join(sqlquote(x) for x in adict.values()),
')',
]
return ''.join(t)

if __name__ == "__main__":
tests = [
('IL', 'Illinois'),
('OH', "O'Hara"),
]
cols = ['state', 'name']
for test in tests:
the_dict = dict(zip(cols, test))
print the_dict
print insertFromDict('statecode', the_dict)

C:\junk>sqlinsdict.py
{'state': 'IL', 'name': 'Illinois'}
INSERT INTO statecode (state, name) VALUES ('IL', 'Illinois')
{'state': 'OH', 'name': "O'Hara"}
INSERT INTO statecode (state, name) VALUES ('OH', 'O''Hara')
 
J

johnzenger

Also, it may be easier to use string interpolation, as in:

return "INSERT INTO statecode (state, name) VALUES ('%(state)s',
'%(name)s')" % insert_dict

....after all necessary escaping, of course.

John said:
len said:
Hi all

I am writing a python program that inserts records into a database on
XP using mxODBC.

I need to write a section of code which will create the following SQL
command as an example;

INSERT INTO statecode (state, name) VALUES ('IL', 'Illinois')

This statement will be built up using the following code;

import mx.ODBC
import mx.ODBC.Windows
def insertFromDict(table, dict):
"""Take dictionary object dict and produce sql for
inserting it into the named table"""
sql = 'INSERT INTO ' + table
sql += ' ('
sql += ', '.join(dict)
sql += ') VALUES ('
sql += ', '.join(map(dictValuePad, dict)) # ??? this code does
NOT format correctly
sql += ')'
return sql

def dictValuePad(key): # ??? this code
does Not format correctly
return "'" + str(key) + "'"

db = mx.ODBC.Windows.DriverConnect('dsn=UICPS Test')
c = db.cursor()
insert_dict = {'state':'IL', 'name':'Illinois'}
sql = insertFromDict("statecode", insert_dict)
print sql
c.execute(sql)
Click to expand...

The code below will do what you say that you want to do -- so long as
all your columns are strings (varchar or whatever in SQL terms).
Otherwise IMHO you would be much better off doing it this way:
sql = "insert into policy (type, premium) values(?, ?)"
data = ('building', 123.45)
cursor.execute(sql, data)
for two reasons:
(1) let the ODBC kit worry about formatting dates, strings with
embedded single quotes, etc
(2) it can be more efficient; the sql is constant and needs to be
parsed only once
(3) [bonus extra reason] the way you are doing it is vulnerable to
what's called an "SQL injection attack"; although you have no doubt
eyeballed all the data, doing it that way is a bad habit to get into.

You should be able to modify the supplied code very easily to produce
the sql variety with "?" in it.

HTH,
John

C:\junk>type sqlinsdict.py
def sqlquote(astring):
return "'" + astring.replace("'", "''") + "'"

def insertFromDict(table, adict):
"""Take dictionary object dict and produce sql for
inserting it into the named table.
Sample input:
insert_dict = {'state':'IL', 'name':'Illinois'}
sql = insertFromDict("statecode", insert_dict)
Required output:
INSERT INTO statecode (state, name) VALUES ('IL', 'Illinois')
"""

t = [
'INSERT INTO ',
table,
' (',
', '.join(adict.keys()),
') VALUES (',
', '.join(sqlquote(x) for x in adict.values()),
')',
]
return ''.join(t)

if __name__ == "__main__":
tests = [
('IL', 'Illinois'),
('OH', "O'Hara"),
]
cols = ['state', 'name']
for test in tests:
the_dict = dict(zip(cols, test))
print the_dict
print insertFromDict('statecode', the_dict)

C:\junk>sqlinsdict.py
{'state': 'IL', 'name': 'Illinois'}
INSERT INTO statecode (state, name) VALUES ('IL', 'Illinois')
{'state': 'OH', 'name': "O'Hara"}
INSERT INTO statecode (state, name) VALUES ('OH', 'O''Hara')
Click to expand...
 
J

John Machin

[email protected] said:
Also, it may be easier to use string interpolation, as in:

return "INSERT INTO statecode (state, name) VALUES ('%(state)s',
'%(name)s')" % insert_dict

...after all necessary escaping, of course.
Click to expand...

Excuse me!? "statecode" needs to come from the first argument. Likewise
the words "state" and "name" are *variables*. The OP has a gazillion
other tables to process -- are you suggesting he should type in a
gazillion different hard-coded return statements when he's already on
the right track and just needs a bit of help with dict.keys() and
dict.values()?
 
S

Steve Holden

len said:
Hi all

I am writing a python program that inserts records into a database on
XP using mxODBC.

I need to write a section of code which will create the following SQL
command as an example;

INSERT INTO statecode (state, name) VALUES ('IL', 'Illinois')

This statement will be built up using the following code;

import mx.ODBC
import mx.ODBC.Windows
def insertFromDict(table, dict):
"""Take dictionary object dict and produce sql for
inserting it into the named table"""
sql = 'INSERT INTO ' + table
sql += ' ('
sql += ', '.join(dict)
sql += ') VALUES ('
sql += ', '.join(map(dictValuePad, dict)) # ??? this code does
NOT format correctly
sql += ')'
return sql

def dictValuePad(key): # ??? this code
does Not format correctly
return "'" + str(key) + "'"

db = mx.ODBC.Windows.DriverConnect('dsn=UICPS Test')
c = db.cursor()
insert_dict = {'state':'IL', 'name':'Illinois'}
sql = insertFromDict("statecode", insert_dict)
print sql
c.execute(sql)

I copied this code off of ASP and I sure it worked for his particular
circumstance but I need to format up the VALUE clause just a bit
different.
Click to expand...
ASP code frequently makes the mistake of bulding SQL statements that
way. I suspect this is because the ASP ADO model makes it difficult to
produce paramtereized queries. In Python, however, the position is very
different, and you should always try to separate the data from the
fieldnames.
I will be working from a dictionary which will be continualy update in
another part of the program and this code is working.
Click to expand...
Well, assuming you would rather be free of SQL inhection errors you
would be much better advised to do something like this:
... """Return SQL statement and data vector for insertion into table."""
... fields = d.keys()
... sql = 'INSERT INTO %s (%s) VALUES(%s)' % (
... table, ",
... ".join(fields),
... ", ".join("?" for f in fields))
... return sql, d.values()
...
>>> sql, data = insertFromDict("statecode", ... {"state": "IL", "name": "Illinois"})
>>> sql 'INSERT INTO statecode (state, name) VALUES(?, ?)'
>>> data ['IL', 'Illinois']
>>>
Click to expand...
Click to expand...
Click to expand...

Then you make the insertion into the database using

c.execute(sql, data)

The other principal advantage of this technique is that you don't need
to discriminate between numeric and string fields, since they are both
handled the same way. You also get better efficiency if you run with the
same fields many times, as the DBMS will (if it's sufficiently
advanced) use the already-prepared version of the statement rather than
recompiling it repeatedly.

regards
Steve
 
S

Steve Holden

Steve Holden wrote:
[...] ^^^^^^^^^^^^
Please ignore the Cygwin mousedroppings ...

regards
Steve
 
D

Dennis Lee Bieber

Hi all

I am writing a python program that inserts records into a database on
XP using mxODBC.

I need to write a section of code which will create the following SQL
command as an example;

INSERT INTO statecode (state, name) VALUES ('IL', 'Illinois')

This statement will be built up using the following code;

import mx.ODBC
import mx.ODBC.Windows
def insertFromDict(table, dict):
"""Take dictionary object dict and produce sql for
inserting it into the named table"""
sql = 'INSERT INTO ' + table
sql += ' ('
sql += ', '.join(dict)
Click to expand...

That, already, is rather less than explicit -- first off, you are
shadowing the builtin "dict"... And you are relying upon it to always
return just the identifying keys.
sql += ') VALUES ('
sql += ', '.join(map(dictValuePad, dict)) # ??? this code does
NOT format correctly
Click to expand...

Don't do that... Use parameterized SQL... What is mxODBC's
substitution character? MySQLdb uses %s.
sql += ')'
return sql

def dictValuePad(key): # ??? this code
does Not format correctly
return "'" + str(key) + "'"
Click to expand...

Parameterized queries let the db-api do the quoting and escapes
needed for you..
db = mx.ODBC.Windows.DriverConnect('dsn=UICPS Test')
c = db.cursor()
insert_dict = {'state':'IL', 'name':'Illinois'}
sql = insertFromDict("statecode", insert_dict)
print sql
c.execute(sql)
Click to expand...

Well, you have different field names from what I'd have used -- "IL"
is a postal abbreviation, and the state is "Illinois" <G>

data = {"abb" : "IL", "state" : "Illinois" }

c.execute("insert into %s (%s) values (%s)"
% ("statecode",
", ".join(data.keys() ),
", ".join(["%s"] * len(data.keys() ) ) ),
data.values() )
# NOTE: only works if data.keys() and data.values() are
# in the same order.
.... % ("statecode",
.... ", ".join(data.keys() ),
.... ", ".join(["%s"] * len(data.keys() )))
insert into statecode (abb, state) values (%s, %s)
MySQLdb also supports the dictionary type substitution syntax

c.execute("insert into %s (%s) values (%s)"
% ("statecode",
", ".join(data.keys() ),
", ".join(["%%(%s)s" % k for k in data.keys()] ) ),
data )
.... % ("statecode",
.... ", ".join(data.keys() ),
.... ", ".join(["%%(%s)s" % k for k in data.keys()] ))
insert into statecode (abb, state) values (%(abb)s, %(state)s)
Find out 1) what is the substitution marker for mxODBC and 2) if it
has a dictionary version.
--
Wulfraed Dennis Lee Bieber KD6MOG
(e-mail address removed) (e-mail address removed)
HTTP://wlfraed.home.netcom.com/
(Bestiaria Support Staff: (e-mail address removed))
HTTP://www.bestiaria.com/
 
J

John Machin

Dennis said:
c.execute("insert into %s (%s) values (%s)"
% ("statecode",
", ".join(data.keys() ),
", ".join(["%s"] * len(data.keys() ) ) ),
data.values() )
# NOTE: only works if data.keys() and data.values() are
# in the same order.
Click to expand...

It is guaranteed, provided you don't mutate the dictionary between
times. In any case, it's a bit hard to imagine under what circumstances
there would be different traversal orders to obtain keys and values :)
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Help don't know what problem is Newbie 2
Semi-Newbie needs a little help 19
Newbie SQL ? in python. 2
ChatBot 4
Perl Newbie Needs Help ASAP! 0
Help with importing from multiple files and printing lines in designated spot to spit out one file. 1
Problem with a login script, SESSION user rights and put this together so it works with the other pages and MySQL. Code examples. 2
How to change star rating color on mouseenter, on mouseout, and on onclick 0

Members online

No members online now.
Total: 197 (members: 0, guests: 197)
Robots: 342

Forum statistics

Threads
474,432
Messages
2,571,682
Members
48,796
Latest member
Greg L.

Latest Threads

Top