How to check if table exists with variable

Question

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?

bobby-tables.com

bruno desthuilliers
– bruno desthuilliers

2020-03-26 13:09:12 +00:00
Commented Mar 26, 2020 at 13:09 — bruno desthuilliers
– bruno desthuilliers, Commented Mar 26, 2020 at 13:09

chepner · Accepted Answer · 2020-03-26 12:48:02Z

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",) )

Collectives™ on Stack Overflow

How to check if table exists with variable

1 Answer 1

Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Related