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:

There can be many reasons for the python error “module not found”.

In my case, I had a laptop with 2.7 and 3.6 both installed.? ?My windows path contained the 2.7 release, but I was running the 3.6 release.? So when I ran from the command line by just typing in “python myprogram.py” it worked.? But when I tried in NotePad++, I was using Python 3.6 and getting the “module not found” error. I also needed to run “easy_install” in Python 3 on the modules that were missing (they were installed in Python 2.7 but not 3.6).

I was pleasantly surprised that in Windows 10, it’s now super easy to update the path.? They parse it for you and give you a special screen:

So I changed the three lines that started with C:\Python27 to C:\Python36.

To find the issue, I learned how to show the version of Python in the Python program itself.


import sys
print ("Python Version:" + sys.version)

For 3.6, the results are shown below: