CODEWITHSUNDEEP
pythonsqlsqlite
nicorellius
nicorellius

Reputation: 4043

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

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.

Upvotes: 0

Views: 873

Answers (1)

CL.
CL.

Reputation: 180080

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

Upvotes: 1

Related Questions