typeerror:查询字典值必须是字符串或字符串的序列

Question

我以前只使用pyodbc和pandas来连接SQL Server来运行查询并将该信息保存到csv文件中。使用这个方法可以工作但结果是警告，我觉得这会减慢程序的速度。

我试图实现sqlchemy，但得到TypeError上面。

我从文档中读取:SQLAlchemy 1.4 Documentation

import pyodbc
import pandas as pd
import numpy as np
from sqlalchemy.engine import URL
from sqlalchemy import create_engine
# Setting display options for the dataframe
pd.set_option('display.max_columns', None)
pd.set_option('max_colwidth', None)
# Turning each row from the main .csv file into a dataframe.
df = pd.read_csv(r'''C:UsersusernameDownloadspy_tma_tables.csv''')
# Turning the dataframe into a list.
tma_table = df['table_name'].tolist()
# Connection setup.
cnxn = pyodbc.connect('DSN=DB_NAME;Trusted_Connection=yes')
connection_url = URL.create("mssql+pyodbc", query={"odbc_connect": cnxn})
engine = create_engine(connection_url)
df_list = []
count = 0
while count < 1:
df1 = pd.read_sql_query("SELECT * FROM " + tma_table[count], engine)
df_list.append(df1)
count += 1
df_count = 0
while df_count < len(df_list):
for item in df_list:
df_list[df_count].to_csv(tma_table[df_count] + ".csv", index=False, header=None, encoding='utf-8')
df_count += 1

运行此命令返回:

TyperError: Query dictionary values must be strings or sequences of strings

Answer 1

找到解决方案:

servername = 'server'
dbname = 'database'
sqlcon = create_engine('mssql+pyodbc://@' + servername + '/' + dbname + '?driver=ODBC+Driver+17+for+SQL+Server')

然后能够插入sqlcon:

df_list = []
count = 0
while count < 1:
df1 = pd.read_sql_query("SELECT * FROM " + tma_table[count], sqlcon)
df_list.append(df1)
count += 1

Answer 2

cnxn = pyodbc.connect('DSN=DB_NAME;Trusted_Connection=yes')
connection_url = URL.create("mssql+pyodbc", query={"odbc_connect": cnxn})

您正在尝试在query=字典中传递pyodbc连接对象。您需要传递连接字符串:

connection_string = "DSN=DB_NAME;Trusted_Connection=yes"
connection_url = URL.create("mssql+pyodbc", query={"odbc_connect": connection_string})