使用python将CSV文件导入到SQL Server [英] Importing a csv file into sql server using python
问题描述
我目前遇到问题,使用先前答案中使用的python编码的较小变体将csv文件导入sql: 将csv插入sql数据库
I'm currently experiencing a problem importing a csv file to sql using a minor variation of python coding used in a previous answer:- Insert csv into sql database
我遇到了以下语法错误的问题:-
I've run into an issue where I get the following syntax error:-
line 28, in insert_records
cursor.execute(insert +'('+ ', '.join(values) +');')
pyodbc.ProgrammingError: ('42000', "[42000] [Microsoft][ODBC Driver 13 for
SQL Server][SQL Server]Incorrect syntax near '/'. (102) (SQLExecDirectW)")
我相信我已经接近成功将这个csv文件导入到sql server中.当前,sql服务器标题中的表已经存在.我已经附上了我正在使用的python代码,该程序终止于[cursor.execute(insert +'('+','.join(values)+');')]
I believe I am close to succeeding into getting this csv file to import into sql server. Currently the table in sql server headings already present. I've attached the python code I am using, the program terminates at [cursor.execute(insert +'('+ ', '.join(values) +');')]
预先感谢, 布莱恩
import pyodbc
import csv
print('connecting')
conn = pyodbc.connect(r'DRIVER={ODBC Driver 13 for SQL Server};'r'SERVER=.\SQLExpress;'r'DATABASE=UFOGBobservations;'r'Trusted_Connection=yes')
print('Connected')
my_cursor = conn.cursor()
print('Cursor established')
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)
cursor.execute(insert +'('+ ', '.join(values) +');')
conn.commit() #must commit unless your sql database auto-commits
table = 'table_1'
mycsv = r'C:\DataAnalystData\UFOGB_Observations.csv' # SET YOUR FILEPATH
insert_records(table, mycsv, my_cursor, conn)
cursor.close()
推荐答案
这可能是一个逃避的问题.如果将这些值作为参数列表传递给execute()
,而不是手动构建字符串,那会更安全.这样可以确保它们正确地逃脱了.
This is possibly an escaping issue. It would be safer if you passed the values as a list of parameters to execute()
rather than manually building a string. This will ensure that they are correctly escaped.
insert = 'INSERT INTO {} ('.format(table) + ', '.join(headers) + ') VALUES ({})' \
.format(', '.join(len(headers) * '?')) # Add parameter placeholders as ?
for row in csvFile:
values = map((lambda x: x.strip()), row) # No need for the quotes
cursor.execute(insert, values) # Pass the list of values as 2nd argument
conn.commit()
这篇关于使用python将CSV文件导入到SQL Server的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!