0

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,Department
Gonzalez,Oliver,1862190394,Chemistry
Roberts,Barbara,1343146197,Computer Science
Carter,Raymond,1460039151,Philosophy
Brian Tompsett - 汤莱恩
  • 5,753
  • 72
  • 57
  • 129
ryan
  • 3
  • 2
  • 5
  • Is the question about reading CSV data in Python, creating SQL inserts in Python, or both? Have you tried anything with these yet? – paisanco Aug 02 '14 at 05:50
  • Quick search on google gave me this Stack Overflow answer: [Python and SQLite insert into table][1] [1]: http://stackoverflow.com/questions/2092757/python-and-sqlite-insert-into-table – Christer Nissen Aug 02 '14 at 07:11
  • @paisanco yes, both .for the read_csv function, i will have to create and insert both. i have created sql_command on creating tables, and executed sql_comand. But how do i insert all the data in the csv file into the table? should i readline, and turn csv into a list? or there is a direct way to do it? – ryan Aug 02 '14 at 14:51
  • For reading the CSV I'd use the Python **csv** module to get it into a list. If using SQLite, the question linked to by @ChristerNissen should be helpful. – paisanco Aug 02 '14 at 17:10

1 Answers1

0

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
    with open(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 '
        for row in 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()
newtothis
  • 35
  • 10