我正在尝试使用Python/pyodbc将一些csv数据插入到Access 2007数据库中.我在Access中测试了一个工作追加查询,它选择了标题和一行值.
INSERT INTO AssetDetails ( [Reporting Account Number], [As Of Date], [Asset Type], [Security Description 1], [Shares/Par], [Base Price], [Base Cost], CUSIP, Ticker, ISIN, SEDOL ) VALUES ("ABCD99020002", "1/31/2010", "CASH & CASH EQUIVALENTS", "INTEREST RECEIVABLE", "0.000", "1.00", "1,171,069.04", "", "", "", "");
在pyodbc中运行此查询会产生:
ProgrammingError: ('42000', "[42000] [Microsoft][ODBC Microsoft Access Driver] Invalid bracketing of name '[0.000]'. (-1002) (SQLExecDirectW)")
为了解决这个问题,我认为有可能是引起麻烦的数字周围的引号.我转到了以下查询:
SQL = '''INSERT INTO AssetDetails([Reporting Account Number], [As Of Date], [Asset Type], [Security Description 1], [Shares/Par], [Base Price], [Base Cost], CUSIP, Ticker, ISIN, SEDOL) VALUES ("AKPF99020002", "1/31/2010", "CASH & CASH EQUIVALENTS", "INTEREST RECEIVABLE", 0.00, 1.00, 1171069.04, "", "", "", "");'''
当我在它上面时,我不妨删除1171069.04号码上每3个数字之间的逗号.这给出了一个错误
Error: ('07002', '[07002] [Microsoft][ODBC Microsoft Access Driver] Too few parameters. Expected 5. (-3010) (SQLExecDirectW)')
我做了一些挖掘,看起来当引用不存在的列时会发生此错误.也许这表明列格式不好?所以我把事情简化为一个非常简单的查询,看看是否有可能在INSERT
我的Access表中有任何内容,只有一个字段(不需要任何字段).
cur.execute('INSERT INTO AssetDetails("SEDOL") VALUES ("123412")')
这也产生了
Error: ('07002', '[07002] [Microsoft][ODBC Microsoft Access Driver] Too few parameters. Expected 1. (-3010) (SQLExecDirectW)')
我也试过上面没有SEDOL周围的引号,或括号......
我慢慢疯了......任何想法?
Access通常接受SQL语句中的任何一个'
或"
作为文本分隔符.但是,我似乎记得Access ODBC驱动程序有问题"
,至少有时候.
所以用单引号试试吧.
SQL = """INSERT INTO AssetDetails
(
[Reporting Account Number],
[As Of Date],
[Asset Type],
[Security Description 1],
[Shares/Par],
[Base Price],
[Base Cost],
CUSIP,
Ticker,
ISIN,
SEDOL
)
VALUES
(
'ABCD99020002',
'1/31/2010',
'CASH & CASH EQUIVALENTS',
'INTEREST RECEIVABLE',
'0.000',
'1.00',
'1,171,069.04',
'',
'',
'',
''
);"""
我的预感是要么可以工作,要么你会得到一个不同的错误.