将CSV插入python中的SQL数据库 [英] Insert CSV into SQL database in python

问题描述

我想将CSV文件中的数据插入到之前创建的表中.所以可以说我创建了一个名为T的表csv_file如下:

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

推荐答案

以由Mumpo共享.

将CSV插入SQL Server时，这对我有用.您只需要提供连接详细信息，文件路径和要写入的表即可.唯一的警告是您的表必须已经存在，因为此代码会将CSV插入到现有表中.

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()

这篇关于将CSV插入python中的SQL数据库的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！