use python variable to read specific rows from access table using sqlalchemy

Question

I have an access table called "Cell_list" with a key column called "Cell_#". I want to read the table into a dataframe, but only the rows that match indices which are specified in a python list "cell_numbers". I tried several variations on:

   import pyodbc
   import pandas as pd
   cell_numbers = [1,3,7]
   cnn_str = r'Driver={Microsoft Access Driver (*.mdb,*.accdb)};DBQ=C:\folder\myfile.accdb;'
   conn = pyodbc.connect(cnn_str)
   query = ('SELECT * FROM Cell_list WHERE Cell_# in '+tuple(cell_numbers))
   df = pd.read_sql(query, conn)

But no matter what I try I get a syntax error. How do I do this?

Parfait · Accepted Answer · 2022-05-18 15:44:48Z

1

Consider best practice of parameterization which is supported in pandas.read_sql:

# PREPARED STATEMENT, NO DATA
query = (
    'SELECT * FROM Cell_list '
    'WHERE [Cell_#] IN (?, ?, ?)'
)

# RUN SQL WITH BINDED PARAMS
df = pd.read_sql(query, conn, params=cell_numbers)

Consider even dynamic qmark placeholders dependent on length of cell_numbers:

qmarks = [', '.join('?' for _ in cell_numbers)]
query = (
    'SELECT * FROM Cell_list '
    f'WHERE [Cell_#] IN ({qmarks})'
)

answered May 18, 2022 at 15:44

Parfait

108k19 gold badges103 silver badges138 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Gustav · Accepted Answer · 2022-05-18 06:49:54Z

0

Convert (join) cell_numbers to text:

cell_text = '(1,3,7)'

and concatenate this.

The finished SQL should read (you may need brackets around the weird field name Cell_#):

SELECT * FROM Cell_list WHERE [Cell_#] IN (1,3,7)

answered May 18, 2022 at 6:49

Gustav

56.5k7 gold badges33 silver badges65 bronze badges

1 Comment

Gustav Over a year ago

Great! Then please mark as answered.

Collectives™ on Stack Overflow

use python variable to read specific rows from access table using sqlalchemy

2 Answers 2

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related