sql_server_create_new_table_and_insert_data.py

import pandas as pd
import csv
import pyodbc # http://mkleehammer.github.io/pyodbc/
import sys, os

USERNAME = 'sa'
PASSWORD = 'password'
SERVER = 'server'
DATABASE = 'DATA'
DRIVERNAME = 'ODBC Driver 13 for SQL Server'

cnxn = pyodbc.connect('Driver={'+DRIVERNAME+'};Server='+SERVER+';Database='+DATABASE+';uid='+USERNAME+';pwd='+PASSWORD)  


#####  using pandas grab first row for column headers and create new table using filename ####

filename =r'C:\table_to_insert.csv'

def sql_server_create_table_using_csv(filename):
    tablename = os.path.basename(filename).split('.')[0] # use filename as tablename    
    data = pd.read_csv(filename, nrows=0)
    columns_count = len(data.columns)
    table_create_columns = []
    for i in data.columns:
         table_create_columns.append(str(i+' VARCHAR(200)'))
    headers = str(table_create_columns).replace(" VARCHAR(100)","")
    table_create_columns = str(table_create_columns).replace("'","")[1:-1]
    cur = cnxn.cursor()
    cur.execute('CREATE TABLE '+tablename+'('+table_create_columns+')')
    cnxn.commit()

sql_server_create_table_using_csv(test)

#############  insert data from csv file https://goo.gl/fRihGh

def sql_server_insert_data_csv(filename):
    with open(filename, 'r') as f:
        tablename = os.path.basename(filename).split('.')[0] # use filename as tablename    
        reader = csv.reader(f)
        columns = next(reader) 
        query = 'insert into ' + tablename + '({0}) values ({1})'
        query = query.format(','.join(columns), ','.join('?' * len(columns)))  # ODBC supports query parameters using a question mark as a place holder in the SQL.
        cursor = cnxn.cursor()
        for data in reader:
           cursor.execute(query, data)
        cursor.commit()

sql_server_insert_data_csv(filename)


#https://gist.github.com/ryan413/68e200ffe8f7a8220e69d331a51e2b70