How can you use python variables in an sql search

Question

For my program I need to link two tables together which I know how to do. However, I only need to select certain fields and they are already stored in a python variable. How can I execute an sql search with only selects the fields from the row containing that variable value?

I am using SQLite and all I can do at the moment is something like

sql1 = '''SELECT *
FROM Dogs INNER JOIN Owners
ON Dogs.DogID = Owners.DogID
Order By Speed desc;
'''

I need to be able to use a variable

What API are you using to execute SQL from within Python? Can you share some code here? — lenz, Aug 23 '15 at 12:00
I want to have it selecting where the DogID is equal to the one saved in my variable — Ben Dent, Aug 23 '15 at 12:35

score 3 · Accepted Answer · answered Aug 23 '15 at 12:40

3

Python manuals should be the primary information source. The standard library directly provides a Sqlite interface with nice examples.

One example just reads as:

# Never do this -- insecure!
symbol = 'RHAT'
c.execute("SELECT * FROM stocks WHERE symbol = '%s'" % symbol)

# Do this instead
t = ('RHAT',)
c.execute('SELECT * FROM stocks WHERE symbol=?', t)
print c.fetchone()

With your own code, it gives:

sql1 = '''SELECT *
FROM Dogs INNER JOIN Owners
ON Dogs.DogID = Owners.DogID
WHERE Dogs.DogID = ?
Order By Speed desc;
'''
c.execute(sql1, dogid)
...

answered Aug 23 '15 at 12:40

Serge Ballesta

143,923
11
122
252

I get an error message when specifying variable above that then using what you said as apparently it uses 1 binding but 7 are supplied – Ben Dent Aug 23 '15 at 12:51
@BenDent Is `DogID` a string of 7 characters? Then you most probably forgot to provide it as a 1-tuple, ie. `c.execute(sql1, (dogid,))`. – lenz Aug 23 '15 at 13:12

Texom512 · Answer 2 · 2015-08-23T12:43:04.217

0

You can search in a SQL database like this:

import sqlite3

db = sqlite3.connect('db.sq3')
cursor = db.cursor()

to_search = ('value', )
found = cursor.execute('SELECT * FROM table WHERE key=?', to_search).fetchall()

edited Aug 23 '15 at 12:43

answered Aug 23 '15 at 12:38

Texom512

4,785
3
16
16

@Serge: That's corrected. – Texom512 Aug 23 '15 at 12:47

score 0 · Answer 3 · answered Aug 23 '15 at 12:43

0

A little example of string formatting in python:

sql1 = '''SELECT * {0}
FROM {1} INNER JOIN {2}
ON {1}.{3} = {2}.{3}
Order By {4} desc;
'''

print(sql1.format('Spot','Dogs', 'Owners', 'DogID', 'Speed'))

answered Aug 23 '15 at 12:43

primero

591
1
6
17

How can you use python variables in an sql search

3 Answers3