我正在尝试使用pandas
和pyodbc
将数据从excel文件推送到sqlserverexpressmanagementstudio2008,因为我需要通过在数据库myDB
中创建表来推送数据
这是我的密码
import pyodbc
import pandas as pd
import os
import sqlalchemy
from sqlalchemy import create_engine
# connect db
engine = create_engine('mssql+pyodbc://WWX542337CDCD\SMARTRNO_EXPRESS/myDB?driver=SQL+Server+Native+Client+11.0')
cursor = engine.raw_connection().cursor()
mydir = (os.getcwd()).replace('\\', '/') + '/'
lte_details = pd.read_excel(r'' + mydir + 'MNM_Rotterdam_5_Daily_Details-20191216081027.xlsx', sheet_name='LTE Details')
lte_details.columns = lte_details.columns.str.replace(' ', '')
# reading and insert one file at a time
for file in os.listdir('.'):
# only process excels files
file_basename, extension = file.split('.')
if extension == 'xlsx':
lte_details.to_sql(file_basename.lower(), con=engine.connect(), if_exists='replace')
我发现这个错误:
Traceback (most recent call last):
File "C:/Users/mwx825326/PycharmProjects/MyReference/test.py", line 22, in <module>
file_basename, extension = file.split('.')
ValueError: not enough values to unpack (expected 2, got 1)
嗯,你可以尝试使用
sqlalchemy
连接。因为pd.read_sql
中的con
使用sqlalchemy engine
。你知道吗你可以试试这个
相关问题 更多 >
编程相关推荐