Python数据库插入 [英] Python database insert
问题描述
我一直在尝试解析文本文件(使用参数 encoding='utf8' 打开)并使用 pyodbc 模块将提取的值插入到 mdb 数据库中.我试过下面的代码:
I've been trying to parse a text file (opened with parameter encoding='utf8') and insert extracted values into an mdb database using pyodbc module. I have tried the code below:
for line in fp:
tokens = line.split('\t')
tokens[4] = tokens[4][:len(tokens[4])-1] #to avoid the \n
tokens[1] = tokens[1][1:] #to remove the 'u' from the beginning of utf8 characters like u'\u0622'
content = conn.execute("INSERT INTO Entries (PForm, WForm, Code, Freq, Pattern) VALUES ("+tokens[0]+","+tokens[1]+","+tokens[2]+","+tokens[3]+","+tokens[4]+")")
conn.commit()
并收到以下错误:错误: ('07002', '[07002] [Microsoft][ODBC Microsoft Access Driver] 参数太少.预期为 4. (-3010) (SQLExecDirectW)')
and received the following error: Error: ('07002', '[07002] [Microsoft][ODBC Microsoft Access Driver] Too few parameters. Expected 4. (-3010) (SQLExecDirectW)')
附言我的文件的第一行是:آ 'A Ab 1 S
P.S. the first line of my file is: آ 'A Ab 1 S
其他行的格式相同.
您的意见将不胜感激:)
Your comments will be appreciated :)
推荐答案
不要在要插入的字符串周围加上引号.假设Freq"行是整数类型:
You don't put quotes around the strings which you want to insert. Assuming the "Freq" row is of type INTEGER:
stmt = """
INSERT INTO Entries (PForm, WForm, Code, Freq, Pattern)
VALUES ('%s', '%s', '%s', %s, '%s')
"""
params = tuple(t for t in tokens)
conn.execute(stmt % params)
但无论如何,您不应该像这样设置 INSERT
语句的格式.您使用的库不提供参数化语句的工具吗?像这样:
But anyway, you shouldn't be formatting an INSERT
statement like this. Doesn't the library you're using provide a facility to parameterize statements ? Something like this:
conn.execute("INSERT INTO Foo VALUES (?, ?, ?)", (foo, bar, baz))
这篇关于Python数据库插入的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!