TyperError：查询字典值必须是字符串或序列

Question

我以前仅使用PyodBC和Pandas接触到SQL Server来运行查询并将该信息保存到CSV文件中。使用此方法确实有效，但导致警告我觉得程序正在减慢程序。

我正在尝试实现Sqlachemy，但要获得上面的TypeError。

我从文档中读到：

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:\Users\username\Downloads\py_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 in：

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 = dict中传递PYODBC连接对象。您需要传递连接字符串：

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