Read .db file into Python

Question

I read the basics on the sqlite3 module (https://docs.python.org/2/library/sqlite3.html) which allows you to connect to a .db file within a database and then you can execute normal SQL commands, but I'm wondering if it's possible to read the .db file into a list of lists, or arrays, or even dictionaries. Essentially, I'm trying to do the same thing that the unicodecsv or csv modules do but with .db files.

In case you're wondering why I'm attempting this: I'm going to be transforming the data and it would just be easier for me to work with an array to compute on an entire column at once. Please let me know if you need any clarifications.

It's not feasible to read a SQLite3 database without using the library. Writing a few `SELECT * FROM table` queries is trivial to do. — Colonel Thirty Two, Feb 28 '16 at 04:52
Does this answer your question? [How to open and convert sqlite database to pandas dataframe](https://stackoverflow.com/questions/36028759/how-to-open-and-convert-sqlite-database-to-pandas-dataframe) — PV8, Apr 17 '20 at 11:04

score 3 · Accepted Answer · edited May 02 '18 at 16:31

3

You will need to iterate over all the tables and convert to list. This is completely untested code, but should give you an idea of what you need to do:

import sqlite3

def importdb(db):
     conn = sqlite3.connect(db)
     c = conn.cursor()
     c.execute("SELECT name FROM sqlite_master WHERE type='table';")
     for table in c.fetchall()
         yield list(c.execute('SELECT * from ?;', (table[0],)))

edited May 02 '18 at 16:31

Toby Speight

27,591
48
66
103

answered Feb 28 '16 at 01:39

AChampion

29,683
4
59
75

Even after putting in a `:` at the end of the line with `for`, I get the error `OperationalError: near "?": syntax error` – Joe Nov 25 '22 at 15:28

score 1 · Answer 2 · answered Feb 28 '16 at 01:36

Breakbotz, I am a little confused as to what you mean by read the db file as a list of lists (without using the sql commands). The file I believe is a binary file with the details on the format here. So if you really want to, you could create a parser to read it but that may be strongly advised against.

If you read the entire contents of the sqlite3 file and then load all that information into list of lists, you are storing all of that in memory, which even if you do have enough memory for, is not the best use of it, especially if the data is huge.

Secondly, once you get the cursor from the sqlite3 module, you could populate a list of lists, chunks at a time using selection argument to filter the columns that you can work at a time which will probably be worth the time rather than trying to parse the sqlite3 file.

I am sorry if I misread what you are trying to do, the scope of it at least, but I think the link may help you parse the data. I have not tried it myself.

Read .db file into Python

2 Answers2