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

I was previously using only pyodbc and pandas to reach out to a SQL Server to run a run a query and save that information into a csv file. Using this method does work but results in warnings that I feel was slowing down the program.

I'm trying to implement SQLAchemy but getting the TypeError above.

I read from the docs: 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:\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

Running this returns:

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

CodePudding user response：

Found a solution:

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

Was then able to plug 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

CodePudding user response：

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

You are trying to pass a pyodbc Connection object in the query= dict. You need to pass the connection string:

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