Insert Csv Into Sql Database In Python

I want to insert the data in my CSV file into the table that I created before. so lets say I created a table named T the csv_file is the following: Last,First,Student Number,Depart

Solution 1:

Building on what was shared by Mumpo.

This has worked for me when inserting a CSV to SQL Server. You just need to provide your connection details, filepath, and the table you want to write to. The only caveat is your table must already exist, as this code will insert a CSV to an existing table.

import pyodbc
import csv

# DESTINATION CONNECTION
drivr = ""
servr = ""
db = ""
username = ""
password = ""
my_cnxn = pyodbc.connect('DRIVER={};SERVER={};DATABASE={};UID={};PWD={}'.format(drivr,servr,db,username,password))
my_cursor = cnxn.cursor()

def insert_records(table, yourcsv, cursor, cnxn):
    #INSERT SOURCE RECORDS TO DESTINATION
    withopen(yourcsv) as csvfile:
        csvFile = csv.reader(csvfile, delimiter=',')
        header = next(csvFile)
        headers = map((lambda x: x.strip()), header)
        insert='INSERT INTO {} ('.format(table) +', '.join(headers) +') VALUES 'forrowin csvFile:
            values= map((lambda x: "'"+x.strip()+"'"), row)
            b_cursor.execute(insert+'('+', '.join(values) +');' )
            b_cnxn.commit() #must commit unless your sql database auto-commits

table=<sql-table-here>
mycsv ='...T.csv' # SET YOUR FILEPATH
insert_records(table, mycsv, my_cursor, my_cnxn)
cursor.close()