Efficient way to pass this variable multiple times

Question

I'm using Pyodbc in Python to run some SQL queries. What I'm working with is actually longer than this, but this example captures what I'm trying to do:

connection = pyodbc.connect(...)
cursor = connection.cursor(...)

dte = '2018-10-24'

#note the placeholders '{}'
query = """select invoice_id
    into #output 
    from table1 with (nolock) 
    where system_id = 'PrimaryColor' 
    and posting_date = '{}' 

    insert into #output
    select invoice_id
    from table2 with (nolock)
    where system_id = 'PrimaryColor'
    and posting_date = '{}'"""

#this is where I need help as explained below
cursor.execute(query.format(dte, dte))

output = pd.read_sql("""select *
                 from #output"""
                 , connection)

In the above, since there are only two '{}', I'm passing dte to query.format() twice. However, in the more complicated version I'm working with, I have 19 '{}', so I'd imagine this means I need to pass 'dte' to 'query.format{}' 19 times. I tried passing this as a list, but it didn't work. Do I really need to write out the variable 19 times when passing it to the function?

Parfait · Accepted Answer · 2018-10-24 18:04:56Z

2

Consider using a UNION ALL query to avoid the temp table needs and parameterization where you set qmark placeholders and in a subsequent step bind values to them. And being the same value multiply the parameter list/tuple by needed number:

dte = '2018-10-24'

# NOTE THE QMARK PLACEHOLDERS
query = """select invoice_id    
           from table1 with (nolock) 
           where system_id = 'PrimaryColor' 
             and posting_date = ? 

           union all

           select invoice_id
           from table2 with (nolock)
           where system_id = 'PrimaryColor'
             and posting_date = ?"""

output = pd.read_sql(query, connection, params=(dte,)*2)

answered Oct 24, 2018 at 18:04

Parfait

108k19 gold badges103 silver badges138 bronze badges

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

Comments

Corentin Limier · Accepted Answer · 2018-10-24 18:05:34Z

1

I agree with the comments, pandas.read_sql has a params argument which prevent from sql injection.

See this post to understand how to use it depending on the database.

Pyodbc has the same parameter on the execute method.

# standard 
cursor.execute("select a from tbl where b=? and c=?", (x, y))

# pyodbc extension 
cursor.execute("select a from tbl where b=? and c=?", x, y)

To answer to the initial question, even if it is bad practice for building SQL queries :

Do I really need to write out the variable 19 times when passing it to the function?

Of course you don't :

query = """select invoice_id
into #output 
from table1 with (nolock) 
where system_id = 'PrimaryColor' 
and posting_date = '{dte}' 

insert into #output
select invoice_id
from table2 with (nolock)
where system_id = 'PrimaryColor'
and posting_date = '{dte}'""".format(**{'dte': dte})

or :

query = """select invoice_id
into #output 
from table1 with (nolock) 
where system_id = 'PrimaryColor' 
and posting_date = '{0}' 

insert into #output
select invoice_id
from table2 with (nolock)
where system_id = 'PrimaryColor'
and posting_date = '{0}'""".format(dte)

Python 3.6+ :

query = f"""select invoice_id
into #output 
from table1 with (nolock) 
where system_id = 'PrimaryColor' 
and posting_date = '{dte}' 

insert into #output
select invoice_id
from table2 with (nolock)
where system_id = 'PrimaryColor'
and posting_date = '{dte}'"""

Note the usage of f before """ ... """

edited Oct 24, 2018 at 18:05

answered Oct 24, 2018 at 17:48

Corentin Limier

5,0861 gold badge16 silver badges24 bronze badges

2 Comments

Corentin Limier Over a year ago

@roganjosh thanks, i mixed up because he uses pandas as well. Edited

erik7970 Over a year ago

Thank you! This did it!

Collectives™ on Stack Overflow

Efficient way to pass this variable multiple times

2 Answers 2

Comments

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related