5

I know this is a very basic question but for some reason I can't get past this one error. I'm trying to show/put all the names of the tables in a database (named 'GData.db') into a variable available_tables in Python. Currently I have the following:

con = sql.connect(r'/Users/linnk/Desktop/Results/GData.db')
cur = con.cursor() 
cur.execute("SHOW TABLES IN GData")
available_table=(cursor.fetchall())

This gives me the following error for the second-last line:

OperationalError: near "SHOW": syntax error

I've looked at the SHOW TABLES documentation as well as around the web but not found information that helps me.

TylerH
  • 20,799
  • 66
  • 75
  • 101
LinnK
  • 385
  • 2
  • 4
  • 17
  • 2
    *What* "SHOW TABLES documentation"? There isn't any, because SHOW TABLES is a MySQL-specific extension and doesn't exist in sqlite. – Daniel Roseman Aug 13 '15 at 11:11
  • 2
    possible duplicate of [How do I list the tables in a SQLite database file](http://stackoverflow.com/questions/82875/how-do-i-list-the-tables-in-a-sqlite-database-file) – Peter Wood Aug 13 '15 at 11:14
  • This documentation: https://dev.mysql.com/doc/refman/5.0/en/show-tables.html Also - to Peter Wood - I looked at that question but it didn't help me figure this out. – LinnK Aug 13 '15 at 11:17
  • 1
    @LinnK `sqlite` and `mysql` are two different databases. – Peter Wood Aug 13 '15 at 14:10
  • Yes, did figure that out:) – LinnK Aug 14 '15 at 04:42

1 Answers1

16

The query to list tables in a Sqlite database:

SELECT name FROM sqlite_master
WHERE type='table'
ORDER BY name;

So your snippet becomes:

con = sql.connect(r'/Users/linnk/Desktop/Results/GData.db')
mycur = con.cursor() 
mycur.execute("SELECT name FROM sqlite_master WHERE type='table' ORDER BY name;")
available_table=(mycur.fetchall())

See the Sqlite FAQ for more info.

giotto
  • 532
  • 3
  • 15
chucksmash
  • 5,777
  • 1
  • 32
  • 41