How can I check if a database table exists or not with Python?

Question

I would like to check if a database table exists or not, but I don't know how to do.

I wrote (for example with SQLite, although I use MySQL mainly),

import sqlite3
table_name = "some_table"
connection = sqlite3.connect(db)
cursor = connection.cursor()
table_check = "SELECT name FROM sqlite_master WHERE type='table' AND name={};".format(table_name)
if not cursor.execute(table_check).fetchone():  # if the table doesn't exist
# OR if cursor.execute(table_check).fetchone() == "":
    create_table()
else:
    update_table()

But, an Error occured and I cannot proceed.

sqlite3.OperationalError: no such column: some_table

I read several Q&A here, but I couldn't get those. Any advice can help me. Thank you.

Python 3.5.1

Shadow · Accepted Answer · 2016-02-18 06:45:39Z

1

The answer is depending on what rdbms product (mysql, sqlite, ms sql, etc.) you use.

You are getting this particular error in your above query because you do not enclose the value of table_name variable in single quotes.

In mysql you can use information_schema.tables table to query if a table exists.

answered Feb 18, 2016 at 6:45

Shadow

34.5k10 gold badges67 silver badges76 bronze badges

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

3 Comments

yamachan Over a year ago

Thank you for your reply, Shadow. Ah, sorry, table_name in format() is variable. Sorry, I might make you confused. I changed. I am using SQLite as a test because it's easy to use. And thank you for the link.

Shadow Over a year ago

Still, you need to enclose the table_name by single quotes. Your sql in the current format will be: ...WHERE type='table' AND name=mytable;, but you need ...WHERE type='table' AND name='mytable';

yamachan Over a year ago

Ah, sorry I misunderstood what you wrote. My English is poor! Yes, you are right, I had to write name='{}'. Thank you for your advice.

Collectives™ on Stack Overflow

How can I check if a database table exists or not with Python?

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related