将csv文件导入python,使用python将CSV文件导入到SQL Server

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

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) +');')]

Thanks in advance,

Bryan

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

解决方案

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

  • 0
    点赞
  • 0
    评论
  • 0
    收藏
  • 一键三连
    一键三连
  • 扫一扫,分享海报

表情包
插入表情
评论将由博主筛选后显示,对所有人可见 | 还能输入1000个字符
©️2021 CSDN 皮肤主题: 深蓝海洋 设计师:CSDN官方博客 返回首页
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、C币套餐、付费专栏及课程。

余额充值