Loop Through First Column (Attribute) of Each Table from SQLite Database with Python

Question

I'm writing a utility to help analyze SQLite database consistencies using Python. Manually I've found some inconsistencies so I thought it would be helpful if I could do these in bulk to save time. I set out to try this in Python and I'm having trouble.

Let's say I connect, create a cursor, and run a query or two and end up with a result set that I want to iterate through. I want to be able to do something like this (list of tables each with an ID as the primary key):

# python/pseudocode of what I'm looking for
for table in tables:
    for pid in pids:
        query = 'SELECT %s FROM %s' % (pid, table)
        result = connection.execute(query)
        for r in result:
            print r

And that would yield a list of IDs from each table in table list. I'm not sure if I'm even close.

The problem here is that some tables have a primary key called ID while others TABLE_ID, etc. If they were all ID, I could select the IDs from each but they're not. This is why I was hoping to find a query that would allow me to select only the first column or the key for each table.

CL. · Accepted Answer · 2014-04-02 21:04:20Z

1

To get the columns of a table, execute PRAGMA table_info as a query. The result's pk column shows which column(s) are part of the primary key:

> CREATE TABLE t(
>     other1 INTEGER,
>     pk1    INTEGER,
>     other2 BLOB,
>     pk2    TEXT,
>     other3 FLUFFY BUNNIES,
>     PRIMARY KEY (pk1, pk2)
> );
> PRAGMA table_info(t);
cid  name    type            notnull  dflt_value  pk
---  ------  --------------  -------  ----------  --
0    other1  INTEGER         0                    0 
1    pk1     INTEGER         0                    1 
2    other2  BLOB            0                    0 
3    pk2     TEXT            0                    2 
4    other3  FLUFFY BUNNIES  0                    0

answered Apr 2, 2014 at 21:04

CL.

182k18 gold badges241 silver badges282 bronze badges

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

Collectives™ on Stack Overflow

Loop Through First Column (Attribute) of Each Table from SQLite Database with Python

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related