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.

Improve this question
5
  • 2
    What "SHOW TABLES documentation"? There isn't any, because SHOW TABLES is a MySQL-specific extension and doesn't exist in sqlite. Commented Aug 13, 2015 at 11:11
  • 2
    possible duplicate of How do I list the tables in a SQLite database file Commented Aug 13, 2015 at 11:14
  • This documentation: 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. Commented Aug 13, 2015 at 11:17
  • 1
    @LinnK sqlite and mysql are two different databases. Commented Aug 13, 2015 at 14:10
  • Yes, did figure that out:) Commented Aug 14, 2015 at 4:42

1 Answer 1

Reset to default
17

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.

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

1 Comment

Thank you! I skipped the ORDER BY part, but otherwise perfect.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.