尝试使用Python在MS-Access数据库中从Excel动态添加列(字段)时出现数据类型转换错误 [英] Data Type Conversion Error while trying to dynamically add columns(fields) from excel in MS-Access database using Python

问题描述

我正在尝试将Excel工作表中的第一行(即列名)填充到ms-access数据库中，但这给了我"数据类型转换错误(3421)".知道为什么会这样吗?

I'm trying to populate the 1st row(i.e. the column names) from an excel sheet to the ms-access database, but it's giving me 'Data type conversion Error(3421)'. Any idea why this is happening ?

from comtypes.client import CreateObject
from xlrd import open_workbook,cellname
import os
from comtypes.gen import Access

access = CreateObject('Access.Application')    

DBEngine = access.DBEngine

db = DBEngine.CreateDatabase('test.mdb', Access.DB_LANG_GENERAL)

excel_file = open_workbook('test_excel_file.xlsx')
work_sheet = excel_file.sheet_by_index(0)

db.BeginTrans()
db.Execute("CREATE TABLE MY_TABLE (ID Text)")

for row_index in range(0, 1):
    for col_index in range(0, work_sheet.ncols):
        cell_value = work_sheet.cell(row_index,col_index).value
        db.Execute("ALTER TABLE MY_TABLE ADD COLUMN %s", cell_value)

db.CommitTrans()
db.Close()

错误:

Traceback (most recent call last):
File "My_DB_Code.py", line 21, in <module>
db.Execute("ALTER TABLE MY_TABLE ADD COLUMN %s", cell_value)
_ctypes.COMError: (-2146824867, None, (u'Data type conversion error.', u'DAO.Dat
abase', u'jeterr40.chm', 5003421, None))

推荐答案

通过参数化查询，可以用参数代替列的 values ，但不能代替表名和列名本身.您将需要为此使用字符串格式，如

A parameterized query allows parameters to be substituted for the values of a column, but not for table and column names themselves. You will need to use string formatting for that, as in

db.Execute("ALTER TABLE MY_TABLE ADD COLUMN [{0}] TEXT(50)".format(cell_value))

这篇关于尝试使用Python在MS-Access数据库中从Excel动态添加列(字段)时出现数据类型转换错误的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！