3

I want to list all the tables of a sqlite3 database, but it doesn’t work. Let’s imagine that in ma database I have the following tables : foo1, foo2, foo3, … Then, with this code :

cur.execute("SELECT name FROM sqlite_master")
for table in cur:
     print("The table is", table[0])

I have this output : The table is foo1, whereas I’d like have this one :

The table is foo1
The table is foo2
The table is foo3
…

But if I modify the code to have :

cur.execute("SELECT name FROM sqlite_master")
print(cur.fetchone())
for table in cur:
     print("The table is", table[0]

Then the output is :

(foo1,)
The table is foo2

So what’s wrong in my code ?

Shan-x
  • 1,146
  • 6
  • 19
  • 44
  • I don’t think so. I have use the link you give to make my code, but it doesn’t work. – Shan-x Aug 02 '13 at 10:47
  • maybe order it by name? `SELECT name FTOM sqlite_master ORDER BY name` – gitaarik Aug 02 '13 at 10:47
  • 1
    It is generally a bad idea for third parties to edit syntax errors out of a question as they often provide clues to what is broken, so I rolled it back. @Shan-x if `FTOM` is not actually in your code, I suggest you edit the question. If these are typos, I suggest that you copy and paste your code rather than re-type it. – msw Aug 02 '13 at 11:19
  • Treating the cursor as an iterator should work fine, and it seems `cur.fetchone()` works, so have you tried testing `cur.fetchall()`? – JAB Aug 02 '13 at 13:05

1 Answers1

2

Your first method should have worked (assuming FTOM is FROM):

import sqlite3
connection = sqlite3.connect(':memory:')
cur = connection.cursor()
cur.execute('CREATE TABLE foo1 (bar INTEGER, baz TIMESTAMP)')
cur.execute('CREATE TABLE foo2 (bar INTEGER, baz TIMESTAMP)')
cur.execute('CREATE TABLE foo3 (bar INTEGER, baz TIMESTAMP)')
cur.execute(
    "SELECT name FROM sqlite_master")
for table in cur:
    print(table[0])

prints

foo1
foo2
foo3

Note, to exclude indices, be sure to add WHERE type='table' to the SQL query.

unutbu
  • 842,883
  • 184
  • 1,785
  • 1,677
  • Yeah, but I can’t do `cur.execute('CREATE TABLE foo1 (bar INTEGER, baz TIMESTAMP)')` because I don’t know the table names. In fact, I don’t want to `print(table[0])`, but to do this : `cur.execute('CREATE TABLE {} (bar INTEGER, baz TIMESTAMP)'.format(table[0])`. Anyway, this doesn’t work as it should, and I don’t know why. – Shan-x Aug 02 '13 at 10:54