How to check a table exists in sqlite3 or not

Question

I am new to SQL. I want to check that a table exist in data base or not. If it does not exist than create it. Also I want to check that a record exist in table or not. If exists than update it with new data, If does not exist than insert a new record in table. Here is the code. I don't know how to check for a existing table,record.

 import sqlite3 as lite con = lite.connect('test.db') cur = con.cursor() con.execute('''CREATE TABLE EMPLOYEE (ITEMA TEXT PRIMARY KEY NOT NULL, ITEMB CHAR(50) NOT NULL, ITEMC CHAR(50), ITEM0 TEXT ITEM1 TEXT ITEM2 TEXT ITEM3 TEXT ITEM4 TEXT);''') con.execute("INSERT INTO EMPLOYEE (ID,NAME,ADDRESS,MOBILE_NUMBER,TRANSPORT_FOR_LUNCH,ONLY_DROP,KIDS,ADULTS) \ VALUES (%s,%s',%s,%s,%s,%s,%s,%s)" %(ia,ib,ic,i0,i1,i2,i3,i4));

the column names of your CREATE TABLE and your INSERT INTO statements do not match — sqlab
– sqlab, Commented Feb 3, 2014 at 16:02

damienfrancois · Accepted Answer · 2014-02-03 07:05:56Z

To test for table existence in sqlite, you can do something like:

 cursor = con.cursor() statement = "SELECT name FROM sqlite_master WHERE type='table';" if (table_name,) in cursor.execute(statement).fetchall(): print "Table %s exists." % table_name

To update or create depending upon pre-existence of a record, sqlite allows the use of replace, which is an alias for insert or replace:

 replace into table_name (columns) values (values)

Instead of INSERT when I use REPLACE in above code. I get below error. Error no such column: A000000:

sqlab · Accepted Answer · 2014-02-03 15:58:20Z

0

CREATE TABLE EMPLOYEE IF NOT EXISTS ..

creates only if it does not exist

edited Feb 3, 2014 at 15:58

answered Feb 3, 2014 at 12:07

sqlab

6,4341 gold badge16 silver badges29 bronze badges

Collectives™ on Stack Overflow

How to check a table exists in sqlite3 or not

2 Answers 2

1 Comment

Comments

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Related