Import Libraries.

import pandas as pd
import numpy as np
import csv
from datetime import datetime
from datetime import date
import calendar
import requests
import pyodbc
import os
import pickle
import copy
import warnings
warnings.filterwarnings('ignore')

Function to get MS Azure SQL Database Login information.

def get_login():
	server = ''
	db = ''
	uid = ''
	passwd = ''
	with open("C:\\Users\\chris\\Documents\\GitHub\\Jupyter-Notebooks\\azure.txt") as f:
		lines = f.read().split("\n")
		for i in lines:
			if i.split("=")[0] == "server":
				server = i.split("=")[1]
			elif i.split("=")[0] == "db":
				db = i.split("=")[1]
			elif i.split("=")[0] == "uid":
				uid = i.split("=")[1]
			elif i.split("=")[0] == "passwd":
				passwd = i.split("=")[1]	
	return (server, db, uid, passwd)

Function to generate and run Insert/Update commands.

def upload_data(cursor, df):
    global added
    global updated

    def get_indexes(pkey_index, row_length):
        return (pkey_index, [x for x in range(row_length) if x not in pkey_index])

    added = 0
    updated = 0

    pkey_index, value_index = get_indexes(pkey_index=[0, 2, 5, 6, 13], row_length=len(df.columns.to_list()))
    columns = df.columns.to_list()
    table_name = 'chesspgn_moves'

    for index,row in df.iterrows():
        insert_str = f'INSERT INTO {table_name} ('
        for index,i in enumerate(columns):
            insert_str += f'{i}'
            if index != len(row)-1:
                insert_str += ', '
        insert_str += ') VALUES ('
        for i in range(len(row)):
            insert_str += '?'
            if i != len(row)-1:
                insert_str += ', '
        insert_str += ')'
        #print(insert_str)
        #print(tuple(row))
        
        update_str = f'UPDATE {table_name} SET '
        for index,i in enumerate(value_index):
            update_str += columns[i] + ' = '
            if row.to_list()[i] == 'NULL':
                update_str += 'NULL'
            elif type(row.to_list()[i]) == str:
                update_str += "'" + row.to_list()[i].replace("'", "''") + "'"
            elif str(row.to_list()[i]) == 'True':
                update_str += '1'
            elif str(row.to_list()[i]) == 'False':
                update_str += '0'
            else:
                update_str += str(row.to_list()[i])
            if index != len(value_index)-1:
                update_str += ', '
        update_str += ' WHERE '
        for index,i in enumerate(pkey_index):
            update_str += columns[i] + ' = '
            if type(row[i]) == str:
                update_str += "'" + row.to_list()[i] + "'"
            else:
                update_str += str(row.to_list()[i])
            if index != len(pkey_index)-1:
                update_str += ' AND '
        #print(row.to_list())
        #print(update_str)

        try:
            cursor.execute(insert_str, tuple(row.to_list()))
            added += 1
        except:
            cursor.execute(update_str)
            updated += 1
        print(added, updated)

Load data to be inserted.

curr_path = os.getcwd()
os.chdir(path='G:\\My Drive\\Datasets\\Chess PGN')
df = pd.read_csv('chesspgn_moves.csv')
df = df.fillna('NULL')
os.chdir(curr_path)

Connect to Database and run function to upload data to SQL Database.

# Connect to the database
server, db, uid, passwd = get_login()
conn_str = 'DRIVER={SQL Server};SERVER=tcp:' + f'{server};PORT=1433;DATABASE={db};UID={uid};PWD={passwd}'
conn = pyodbc.connect(conn_str)
cursor = conn.cursor()

# Iterate over each row and upload to database.
# ['Drafts', 'Picks', 'Matches', 'Matches_Inverted', 'Games', 'Games_Inverted', 'Plays']
upload_data(cursor=cursor, df=df)

conn.commit()
conn.close()