在 Python 中将 CSV 空白单元格转换为 SQL NULL

Question

我正在尝试将csv文件中的空白单元格转换为NULL，并将它们上传到SQL Server表中，以便它显示为NULL而不是空白。 下面的代码有效，但它们将 NULL 加载为字符串。你能帮我修改代码，让它在SQL中加载NULL吗？

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

Answer 1

这应该有效

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

Answer 2

我找到了这样做的好方法。步骤一：在数据库中创建一个函数，如下所示：

CREATE FUNCTION dbo.ConvertBlankToNull(@tag nvarchar(100))
RETURNS varchar(100)
WITH EXECUTE AS CALLER
AS
BEGIN
Select  @tag=IIF(@tag='',NULL,@tag)
Return(@tag);
END;
GO

第 2 步：请参阅下面的 Cursor.execute 语句。

cursor.execute（"INSERT INTO dbo.NTable （CustomerID）
Select dboconvertBlanktonull（？） "，
row['Company.internalid']）