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)

Functions to generate and run Alter Table commands.

def alter_cols(cursor, df, type_dict):
    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])

    pkey_index, value_index = get_indexes(pkey_index=[0, 14], row_length=len(df.columns.to_list()))
    columns = df.columns.to_list()
    table_name = 'chesspgn_moves'
    rows = df

    for i in columns:
        alter_str = f'ALTER TABLE {table_name} ALTER COLUMN {i} '
        if type_dict[i][0] == str:
            alter_str += f'VARCHAR({type_dict[i][1]})'
        elif type_dict[i][0] == int:
            alter_str += 'INT'
        elif type_dict[i][0] == float:
            alter_str += 'DECIMAL'
        elif type_dict[i][0] == bool:
            alter_str += 'BIT'
        if type_dict[i][2] == False:
            alter_str += ' NOT NULL'
        elif type_dict[i][2] == True:
            alter_str += ' NULL'
        print(alter_str)
        cursor.execute(alter_str)

def set_pkeys(cursor, table_name, pkeys):
    alter_str = f'ALTER TABLE {table_name} ADD CONSTRAINT pk_column_name PRIMARY KEY ('
    for index,i in enumerate(pkeys):
        alter_str += f'{i}'
        if index != len(pkeys)-1:
            alter_str += ', '
    alter_str += ')'
    print(alter_str)
    cursor.execute(alter_str)

Load column data.

df = pd.read_csv('C:\\Users\\chris\\Documents\\GitHub\\Scripts\\chesspgn_moves.csv')
type_dict = pickle.load(open('C:\\Users\\chris\\Documents\\GitHub\\Scripts\\pgn_col_types',"rb"))

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.
alter_cols(cursor=cursor, df=df, type_dict=type_dict)
set_pkeys(cursor=cursor, table_name='chesspgn_moves', pkeys=['W_Name', 'B_Name', 'Date', 'Time', 'Move_Num'])

conn.commit()
conn.close()