Patch for get_table_list() for SQLite3

[Swaroop C H]

I've attached a patch for implementation of get_table_list() for SQLite3 in django/core/db/backends/sqlite3.py

I'm not sure if any Unicode issues are to be taken care of.

Index: sqlite3.py
===================================================================
--- sqlite3.py	(revision 618)
+++ sqlite3.py	(working copy)
@@ -119,7 +119,13 @@
         return "%i-%02i-%02i 00:00:00" % (dt.year, dt.month, dt.day)
 
 def get_table_list(cursor):
-    raise NotImplementedError
+    '''See http://www.sqlite.org/faq.html#q9 on how to list all tables in a SQLite database.'''
+    cursor.execute('''
+        SELECT name FROM sqlite_master
+        WHERE type='table'
+        ORDER BY name
+    ''')
+    return [row[0] for row in cursor.fetchall()]
 
 def get_relations(cursor, table_name):
     raise NotImplementedError

Thanks!
Swaroop
www.swaroopch.info

[Adrian Holovaty]

Thanks very much for this patch. I've integrated it in my local copy, but django-admin.py inspectdb still doesn't work, because the Python SQLite bindings don't have a cursor.description.

Traceback (most recent call last):
  File "/usr/local/bin/django-admin.py", line 120, in ?
    main()
  File "/usr/local/bin/django-admin.py", line 77, in main
    for line in ACTION_MAPPING[action](param):
  File "/usr/lib/python2.3/site-packages/django/core/management.py", line 474, in inspectdb
    for i, row in enumerate(cursor.description):
TypeError: iteration over non-sequence

Any idea on how to get this working in SQLite?

[swaroop@…]

It looks like cursor.description should be checked whether it is None or it is a tuple:

$ python
Python 2.4.1 (#1, Aug 28 2005, 11:58:32) 
[GCC 4.0.0 20041026 (Apple Computer, Inc. build 4061)] on darwin
Type "help", "copyright", "credits" or "license" for more information.
>>> from pysqlite2 import dbapi2 as Database
>>> db = Database.connect('sqlite_tester.db')
>>> cur = db.cursor()
>>> cur.execute('''select name from sqlite_master where type='table' order by name''')
>>> tables = [row[0] for row in cur]
>>> for table in tables:
...     cur.execute('''select * from %s limit 1''' % table)
...     print table, '->', cur.description
... 
auth_admin_log -> None
auth_groups -> None
auth_groups_permissions -> None
auth_messages -> None
auth_permissions -> (('id', None, None, None, None, None, None), ('name', None, None, None, None, None, None), ('package', None, None, None, None, None, None), ('codename', None, None, None, None, None, None))
auth_users -> None
auth_users_groups -> None
auth_users_user_permissions -> None
content_types -> (('id', None, None, None, None, None, None), ('name', None, None, None, None, None, None), ('package', None, None, None, None, None, None), ('python_module_name', None, None, None, None, None, None))
core_sessions -> None
flatfiles -> None
flatfiles_sites -> None
packages -> (('label', None, None, None, None, None, None), ('name', None, None, None, None, None, None))
redirects -> None
sites -> (('id', None, None, None, None, None, None), ('domain', None, None, None, None, None, None), ('name', None, None, None, None, None, None))

On the other hand, the command line sqlite3 gives the schema without problems:

swaroopch@g2swap [~/Sites/sqlite_tester]$ sqlite3 sqlite_tester.db 
SQLite version 3.1.3
Enter ".help" for instructions
sqlite> .schema auth_admin_log
CREATE TABLE auth_admin_log (
    id integer NOT NULL PRIMARY KEY,
    action_time datetime NOT NULL,
    user_id integer NOT NULL REFERENCES auth_users (id),
    content_type_id integer NULL REFERENCES content_types (id),
    object_id text NULL,
    object_repr varchar(200) NOT NULL,
    action_flag smallint unsigned NOT NULL,
    change_message text NOT NULL
);
sqlite> .exit

I'll update more after I get back home from work ;-)

[swaroop@…]

This works though:

>>> for table in tables:
...     cur.execute('''select sql from sqlite_master where type='table' and name='%s' ''' % table)  
...     print table, '->', cur.fetchall()
... 

Explanation at ​http://www.sqlite.org/faq.html#q9 :

The SQLITE_MASTER table looks like this:

CREATE TABLE sqlite_master (
  type TEXT,
  name TEXT,
  tbl_name TEXT,
  rootpage INTEGER,
  sql TEXT
);

Can this workaround be used? Maybe we can add a separate getter module function to get the schema similar to get_table_list() ?

[Adrian Holovaty]

(In [1484]) Fixed #460 -- Added 'django-admin.py inspectdb' support for SQLite. Thanks, Swaroop