DingoGetter February 2016

How can I search an entire database in Python's sqlite3?

New to sqlite3... I have constructed a database in python using sqlite3. The database has hundreds of tables. A sample table lists, say, the average age of people as a function of latitude and longitude. What command can I use to search the entire database (all tables) for "Avg. Age" values where lat > 15, and lon < 30? Sample tables are as follows:

Table 1:

_Avg. age_|__Latitude__|__Longitude__
     15   |    19.86   |     21.5
     21   |    19.86   |     21.5
     75   |    19.86   |     21.5
     16   |    19.86   |     21.5
     .    |      .     |       . 
     .    |      .     |       . 
     .    |      .     |       . 

Table 2:

_Avg. age_|__Latitude__|__Longitude__
     75   |   -22.65   |     53.5
     65   |   -22.65   |     53.5
     12   |   -22.65   |     53.5
     31   |   -22.65   |     53.5
     16   |    19.86   |     53.5
     .    |      .     |       . 
     .    |      .     |       . 
     .    |      .     |       . 

Answers


Joran Beasley February 2016

you need to iterate over all the tables and select from each one

for table in db.execute("SELECT name FROM my_db.sqlite_master WHERE type='table'").fetchall():
    print db.execute("SELECT * FROM %s WHERE lat > 15  and lon < 30"%table).fetchall()

I guess

Post Status

Asked in February 2016
Viewed 3,435 times
Voted 7
Answered 1 times

Search




Leave an answer