SQL Server Create New Table And Insert Data using Python

Python Script to insert CSV File into SQL Server Database

import pandas as pd

import csv

import 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)   cur = cnxn.cursor()

#####  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.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)))
        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