CODEWITHSUNDEEP
pythonsqlpandasms-accesspyodbc
user2978216
user2978216

Reputation: 568

Pandas read_sql with placeholders. 'Where cause' an error

Here is MS Access database, it contains a table. I'd like to read it into DataFrame. The issue here is when "WHERE" is used it causes pyodbc.Error: ('07002', '[07002] [Microsoft][ODBC Microsoft Access Driver] Too few parameters. Expected 1. (-3010) (SQLExecDirectW)

import pyodbc #conda install -c anaconda pyodbc
import pandas as pd
import os
db_path=os.path.realpath("test01.accdb")
conn_str = (
    r"DRIVER={Microsoft Access Driver (*.mdb, *.accdb)};"
    f"DBQ={db_path};"
    )

#sql_str='''SELECT Table1.* FROM Table1''' # << this one works    
sql_str='''SELECT Table1.* FROM Table1 WHERE (((Table1.Column01)="DDD"))''' # << this one doesn't work

with pyodbc.connect(conn_str) as conn:    
    df=pd.read_sql(sql_str, conn)
print(df.head())

Database:
Column01 Column02
AAA BBB
CCC DDD
УУУ ГГГ

Upvotes: 0

Views: 2740

Answers (2)

user2978216
user2978216

Reputation: 568

Ah solved it. This is my solution:

import pyodbc #conda install -c anaconda pyodbc
import pandas as pd
import os
db_path=os.path.realpath("test01.accdb")
conn_str = (
    r"DRIVER={Microsoft Access Driver (*.mdb, *.accdb)};"
    f"DBQ={db_path};"
    )
sql_str='''SELECT Table1.* FROM Table1 WHERE Table1.Column01=?'''

with pyodbc.connect(conn_str) as conn:    
    df=pd.read_sql(sql_str, conn, params=("CCC",))
print(df.head())

Upvotes: 1

FlipperPA
FlipperPA

Reputation: 14311

To answer your original question, try it without the parentheses:

sql_str = '''SELECT Table1.* FROM Table1 WHERE Table1.Column01 = "DDD"'''

However, if you're ever using a variable, it is best to use placeholders to validate your data against the column type in the WHERE clause and avoid possible SQL injection. Good luck!

Upvotes: 0

Related Questions