Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

ALTER TABLE Sqlite: how to check if a column exists before alter the table?

I need to execute in python a SQL query that adds a new column, in sqlite3.

The problem is that sometimes it already exists. So previous to executing the query I need to check if the column already exists.

If it does, then I won't execute the query.

Is there a way in sqlite to do that? Or do I have to make it through a try-catch block in python code?

Thanks a lot in advance!

like image 817
Esabe Avatar asked Mar 01 '10 08:03

Esabe


People also ask

How do you check if a table has a column SQLite?

Checking if Columns Exist in the SQLite Databasereturns an array of DataRows. If the column doesn't exist an array of length 0 will be returned, hence the . Length !=


2 Answers

You can get a list of columns for a table via the following statement:

PRAGMA table_info('table_name');

More details on the pragma commands are availabel at the sqlite web site

like image 54
My Other Me Avatar answered Sep 27 '22 19:09

My Other Me


IMO this

conn = sqlite3.connect(':memory:')
c = conn.cursor()
try:
    c.execute('ALTER TABLE mytable ADD COLUMN newcolumn;')
except:
    pass # handle the error
c.close()

is a better choice than constructing special case queries.

You can wrap the above code in a AddColumn(cursor, table, column) function so you can reuse it,
plus it'll make the code more readable.

like image 35
Nick Dandoulakis Avatar answered Sep 27 '22 17:09

Nick Dandoulakis