在Python中将CSV空白单元格转换为SQL NULL [英] Convert CSV Blank cell to SQL NULL in Python
本文介绍了在Python中将CSV空白单元格转换为SQL NULL的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
我正在尝试将csv文件中的空白单元格转换为NULL并将其上载到SQL Server表中,以便它显示为NULL而不是空白.下面的代码可以工作,但是它们将NULL作为字符串加载.您能帮我修改代码,以便在SQL中加载NULL吗?
I'm trying to convert blank cells in a csv file to NULL and upload them in SQL Server table so it shows as NULL rather blank. below code works but they load NULL as a string. Can you please help me to modify the code so it loads NULL in SQL ?
reader = csv.reader(f_in) # setup code
writer = csv.writer(f_out)
row = next(reader) # handle first line (with no replacements)
writer.writerow(row)
last_row = row # always save the last row of data that we've written
variable = None
for row in reader: # loop over the rest of the lines
row = [x if x else "NULL" for x, y in zip(row, last_row)] # replace empty strings
writer.writerow(row)
last_row = row
with open(outputFileName,'r') as fin: # `with` statement available in 2.5+
dr = csv.DictReader(fin) # comma is default delimiter
to_db = [(i['SubFund'],
i['Trader'],
i['Prime Broker/Clearing Broker'])
cur.executemany("INSERT INTO Citco_SPOS (" +
"subfund, " +
"trader, " +
"prime_broker_clearing_broker, " + +
"VALUES (?, ?, ?);", to_db)
con.commit()
推荐答案
这应该有效
import pyodbc
import csv
cnxn = pyodbc.connect(connection string)
cur = cnxn.cursor()
query = "insert into yourtable values(?, ?)"
with open('yourfile.csv', 'rb') as csvfile:
reader = csv.reader(csvfile, delimiter=',')
for row in reader:
for i in range(len(row)):
if row[i] == '':
row[i] = None
cur.execute(query, row)
cur.commit()
这篇关于在Python中将CSV空白单元格转换为SQL NULL的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文