pypyodbc insert/update

pypyodbc_insert_update.py

import pypyodbc  # C:\Python34\Scripts>pip install pypyodbc

def printu(s):
	try: print(s)
	except: print(str(s).encode('ascii', errors='xmlcharrefreplace'))

con = pypyodbc.connect('DRIVER={SQL Server};SERVER=xxx;DATABASE=xxx;UID=xxx;PWD=xxx')
cur = con.cursor()

table = "reports"
cur.execute("select top 1 id, etc from "+table)
columns = cur.description
cols = [col[0] for col in columns]
insert_sql = "insert into "+table+"(["+'],['.join(cols)+"]) values ("+','.join('?' for i in cols)+")"
update_sql = "update "+table+" set ["+']=?,['.join(cols[1:])+"]=? where ["+cols[0]+"]=?"
# http://samsaffron.com/blog/archive/2007/04/04/14.aspx
combined_sql = """begin tran
if exists (select * from """+table+""" with (updlock, serializable) where ["""+cols[0]+"""] = ?)
begin
	"""+update_sql+"""
end
else
begin
	"""+insert_sql+"""
end
commit tran
"""

json = {"reports": ({"id":1, "etc":"A"}, {"id":2, "etc":"B"}, {"id":3, "etc":"C"})}
# See last snippet for getting json data from a web API.

for r in json["reports"]:
	printu(r)
	vals = []
	for i, c in enumerate(cols):
		col_type = columns[i][1]
		try: v = r[c]
		except KeyError: v = None
		if col_type == str and v and len(v) > columns[i][2]:
			raise ValueError("%s too long" % c)
		vals.append(v)
	
	try:
		id = vals[0]
		printu("Updated %s." % cur.execute(combined_sql, [id] + vals[1:] + [id] + vals).rowcount)
		cur.connection.commit()
	except pypyodbc.Error as er:
		printu(er)