How to execute SQL query with parameters in Pandas

Question

I am using pyscopg2 and have a method that returns a list based on a SQL query. The below works fine...

def checkAnalysisStartDate(self):
    session = self.connection()
    cursor = session.cursor()
    ids = self.getAnalysisIds() # this is a list of integers
    cursor.execute("select start_date from analysis_run_history where analysis_id in %s", [tuple(ids)])
    final_result = [i[0] for i in cursor.fetchall()]

I want to pass the same list of integers but this time return a DataFrame from the results. When I try this though....

import pandas.io.sql as sqlio

def getAnalysisMetaStatsDF(self):
    session = self.connection()
    ids = self.getAnalysisIds() # this is a list of integers
    data = sqlio.read_sql_query("Select * from analysis_stats where analysis_id in %s", [tuple(ids)], session)
    print(data)   

I get back...

AttributeError: 'list' object has no attribute 'cursor'

I think it's something with the way I am passing the parameters to read_sql_query() but I am not sure how to fix it

Answer 1

Whenever you call a function make sure that the call matches its signature. You can look up the signature by running help(sqlio.read_sql_query), typing sqlio.read_sql_query? in IPython or in the documentation. You will find something like this:

Signature:
read_sql_query(
    sql,
    con,
    index_col=None,
    coerce_float: 'bool' = True,
    params=None,
    parse_dates=None,
    chunksize: 'int | None' = None,
    dtype: 'DtypeArg | None' = None,
) -> 'DataFrame | Iterator[DataFrame]'

Notice that the second parameter is the database connection object, not the parameters of the query. In your case a call like the following is more appropriate:

sqlio.read_sql_query(query_string, session, params = ids)

Answer 2

import mysql.connector
from mysql.connector import Error
from sqlalchemy import create_engine
import pandas as pd

engine = create_engine("mysql://root:admin@localhost/pythondb", pool_size=10, max_overflow=20)

table = pd.read_sql("select *From emp", engine)