CODEWITHSUNDEEP
pythonsqlite
ben121
ben121

Reputation: 897

How to check if table exists with variable

I have a sqlite database which using the below I am able to successfully check if a table exists. 

conn = sqlite3.connect('test.db)
c = conn.cursor()

c.execute('''SELECT count(name) FROM sqlite_master WHERE type='table' AND name = 'March' '''

However as soon as I introduce a variable into this code:

c.execute('''SELECT count(name) FROM sqlite_master WHERE type='table' AND name={} '''.format('March')

I get the below error:

c.execute('''SELECT count(name) FROM sqlite_master WHERE type='table' AND name={}'''.format('March'))
sqlite3.OperationalError: no such column: March

Is there a better way of doing this or am I missing something?

Upvotes: 0

Views: 142

Answers (1)

chepner
chepner

Reputation: 532518

You still need to quote the column name, as you are dynamically building a query.

c.execute(
    '''SELECT count(name)
       FROM sqlite_master
       WHERE type='table' AND name='{}'
    '''.format('March')
)

However, a much safer way to write such a query is to let c.execute itself insert the value, rather than using string interpolation (which doesn't ensure the value is correctly escaped).

c.execute(
    '''SELECT count(name)
       FROM sqlite_master
       WHERE type='table' AND name=?
    ''',
    ("March",)
)

Upvotes: 1

Related Questions