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 pypyodbc
import sys
print ("Python Version:" + sys.version)

connection = pypyodbc.connect('Driver={SQL Server};'
                                'Server=mydb.x000joynbxx.us-west-2.rds.amazonaws.com;'
                                'Database=demodb;'
                                'uid=myuser;pwd=mypass')
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) 
		cursor.execute(
			"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] 
			cursor.execute(SQLCommand,Values) 
			connection.commit()
			print ("Data Stored:" + domainName)
		else: 
			print ("Updating to EBN for domain=" + domainName)
			SQLCommand = "UPDATE SEOWebSite set seows_host_id = 4 where seows_name = ?"; 
			Values = [domainName] 
			cursor.execute(SQLCommand,Values) 
			connection.commit()
connection.close()
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