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.

<pre>
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")
</pre>

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: