python - 将数据从 MSSQL 导出到 Excel 并将日期时间转换为日期

Question

我构建了一个查询以从 MSSQL 数据库中导出表的两列。一列具有类型datetime。我想在将其放入dateExcel 文件之前将其转换为。

当我想将它导入 MySQL 数据库时，我得到了错误

警告：（1265，“第 1 行的列 'dLieferdatum' 的数据被截断”）

并且datetime不会导入到 MySQL 列中。但是当我手动将其更改date为 Excel 文件时，它确实会导入。

我的代码：

import pyodbc
import pandas as pd
import datetime
import MySQLdb
import xlrd
import pymysql
import mysql.connector
from mysql.connector import Error
from mysql.connector import errorcode
from datetime import datetime

conn = pyodbc.connect('Driver={SQL Server};'
                      'Server=DESKTOP-XXXX\DEVELOPMENT;'
                      'Database=XXXXXXXXXX;'
                      'Trusted_Connection=yes;')

cursor = conn.cursor()
cursor.execute('SELECT cArtNr, dLieferdatum FROM XXXXXXXXXX.dbo.tLieferantenBestellungPos  WHERE dLieferdatum > CURRENT_TIMESTAMP ')

for row in cursor:
    print(row.strftime("%Y-%m-%d"))

depts = ['cArtNr','dLieferdatum']
depts_query_string = "('{query_vals}')".format(query_vals="','".join(depts))

query = """
                            SELECT cArtNr, dLieferdatum
                            FROM XXXXXXXXXX.dbo.tLieferantenBestellungPos
                            WHERE dLieferdatum > CURRENT_TIMESTAMP
                            """.format(query_vals=depts_query_string)

target = r'O:\\Example'

P_data = pd.read_sql(query, conn)
P_data.to_excel('C:\Datengrab\Python\Export.xlsx')

book = xlrd.open_workbook('C:\Datengrab\Python\Export.xlsx')
sheet = book.sheet_by_index(0)



# MySQL Import Part
# Connect to the database
connection = pymysql.connect(host='XXXXXX.XXXXXXX.XXXXXXX',
                             user='XXXXXXXX',
                             password='XXXXXXXXX',
                             db='XXXXXXX')



cursor = connection.cursor()
query = """INSERT INTO XXXXXXXXX.jll99_deliverytime_import (cArtNr, dLieferdatum) VALUES (%s, %s)"""

for r in range(1, sheet.nrows):
                cArtNr        = sheet.cell(r,1).value
                dLieferdatum  = sheet.cell(r,2).value


                values = (cArtNr, dLieferdatum)

                cursor.execute(query, values)

connection.commit()
cursor.close()

标签： pythonmysqlsql-server