sql server - Python / pypyODBC: Row Insert Using String and NULLs -

i pretty new python , have not interacted sql server , xml parsing until current project (might not best approach take). long story short, team back-logged , need data sandbox table (parsed xml).

there segment of xml contains attributes [xx]001 [xx]025. not of these attributes included each xml parsing. therefore, iterate through loop of possible attributes , append results list. since not attributes in each xml, can incur nonetypes trying represent each one. not issue until creating sql insert statement need them converted nulls. there simple way handle this, newbie status impeding progress. relevant pieces of code follow...

maybe there better way in general? concerned sqllist might able large before hit limit well.

#this list contains nones sqllist = (", ".join(map(repr,appendedlist)))  #the nones issue when here curs.execute("use sandbox insert mytable values (%s)" % (sqllist)) curs.commit() 

here example of sqllist looks like:

'20_2014', '20_2014_3/25/2015 2:01 pm', 'fbr', 'a', '0', '0', '3', '1', '134', none, none, '0', none, '0', '0', '0', '0', '0', none, none, '2', none, none, none, none 

i following error message:

pypyodbc.programmingerror: ('42s22', "[42s22] [microsoft][odbc sql server driver][sql server]invalid column name 'none'.") 

in general still know if "right" way this.

your solution still dynamic sql, suffers

• sql injection issues (e.g., if 1 of strings contains single quote?),
• type juggling (e.g., none -> null in question),
• having use right delimiters literal values (strings, dates, etc.)

all of issues go away if use parameterized query. example uses 2 attributes clarity (instead of 25 in question), principle same. notice don't have special handle none values in order them inserted nulls.

import pypyodbc mydata = [     (1, 'foo'),     (2, none),     (3, 'bar'),     ] connstr = """ dsn=mydb_sqlexpress; """ cnxn = pypyodbc.connect(connstr) crsr = cnxn.cursor() sql = """ insert mytable values (?, ?) """ datarow in mydata:     print(datarow)     crsr.execute(sql, datarow) cnxn.commit() crsr.close() cnxn.close() 

the console output ...

(1, 'foo') (2, none) (3, 'bar') 

... , 3 rows inserted table correctly, including null (none) in second row.