python - PYODBC 将数据插入日期时间列会产生格式不正确的表

Question

我目前正在编写一个程序，该程序将从 Excel 电子表格中获取数据并将其插入到我在程序中创建的 SQL Server 表中。

我之前已将日期时间列指定为 nvarchar(250) ，以便使整个程序正常工作，但是当我将其更改为日期时间时，数据被输入到错误的列中？其余代码也适用于 nvarchar 数据类型。

import pyodbc

connection_string = r'connection_string'
data = 'file_path'

conn = pyodbc.connect(connection_string)
cur = conn.cursor()

createtable = """
create table table1(
    ID Int NULL,
    Date datetime(250) NULL,
    City nvarchar(250) NULL,
    Country nvarchar(250) NULL,
    Image nvarchar(250) NULL,
    Length nvarchar(250) NULL,
    Date_Of_capture nvarchar(250) NULL,
    Comments nvarchar(1000) NULL
    )"""

truncatetable = """truncate table table1"""

with open(data) as file:
    file.readline()
    lines = file.readlines()

if cur.tables(table="table1").fetchone():
    cur.execute(truncatetable)
    for line in lines:
        cols = line.split(',')
        cols = line.replace("'", "")
        sql = "INSERT INTO table1 VALUES({}, '{}', '{}', '{}', '{}', '{}','{}','{}')".format(cols[0], cols[1],cols[2], cols[3], cols[4], cols[5], cols[6], cols[7])
        cur.execute(sql)
else:
    cur.execute(createtable)
    for line in lines:
        cols = line.split(',')
        sql = "INSERT INTO table1 VALUES({}, '{}', '{}', '{}', '{}', '{}','{}','{}')".format(cols[0], cols[1],cols[2], cols[3], cols[4], cols[5], cols[6], cols[7])
        cur.execute(sql)

conn.commit()

conn.close()

我希望日期列显示为日期时间数据类型，同时包含在一列中，但是它会更改表格，以便所有列都不正确并且日期的每个数字都在不同的列中？

非常感谢任何帮助。谢谢。

Answer 1

考虑以下最佳实践:

• 始终指定 INSERT INTO 中的列甚至SELECT子句，具体使用 INSERT INTO myTable (Col1, Col2, Col3, ...)这有助于提高可读性和可维护性；

• 在准备好的语句中使用参数化，以避免在其他重要项目中出现引号转义或类型转换。此外，Python 允许将元组传递到 cursor.execute() 的 params 参数中。而不列出每个单独的列。

• 使用 csv Python 库，用于使用列表或字典遍历 CSV 文件以进行正确对齐并避免内存密集型 .readlines()称呼;

• 合并CREATE TABLE和TRUNCATE在一次 SQL 调用中以避免 if带有游标获取调用的条件。

查看调整后的代码。

import csv
...

action_query = """
    IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = N'mytable')
      BEGIN
        TRUNCATE TABLE table1
      END
    ELSE
      BEGIN
        CREATE TABLE table1(
           ID Int NULL,
           Date datetime NULL,
           City nvarchar(250) NULL,
           Country nvarchar(250) NULL,
           Image nvarchar(250) NULL,
           Length nvarchar(250) NULL,
           Date_Of_capture nvarchar(250) NULL,
           Comments nvarchar(1000) NULL
        )
      END
""")

cur.execute(action_query)
conn.commit()

# PREPARED STATEMENT
append_query = """INSERT INTO mytable (ID, Date, City, Country, Image, 
                                       Length, Date_Of_capture, Comments)
                  VALUES (?, ?, ?, ?, ?, ?, ?, ?)
               """

# ITERATE THROUGH CSV AND INSERT ROWS
with open(mydatafile) as f:
    next(f) # SKIP HEADERS
    reader = csv.reader(f)

    for r in reader:
        # RUN APPEND AND BIND PARAMS
        cur.execute(append_query, params=r)
        conn.commit()

cur.close()
conn.close()