I have already written an article about using a SQLite database from R. I was playing recently with some data, scraped from a web site to text files, that I wanted to transform into a SQLite database. I decided to do it in python, using the sqlite3 module. After a few experiments, I noticed that the SELECT queries on SQLite return a list of tuples. This can be verified as follows, the following code opens a SQLite database and creates a cursor. On this cursor a query is run to return all the SQL statements necessary to drop all tables, one after the other :
|
1 2 3 4 5 6 7 8 9 10 11 12 13 |
import sqlite3 db=sqlite3.connect("EM.db") cursor=db.cursor() # This shall return the list of drop table statements data = cursor.execute("select 'drop table ' || name || ';' from sqlite_master where type = 'table';") all_rows = cursor.fetchall() # This shall return the types... print (type(all_rows)) print (type(all_rows[0])) print (all_rows[0]) |
The result shall be this:
|
1 2 |
<class 'list'=""> <class 'tuple'=""> |
That’s OK. However, it would be nice if I could get them in a Pandas dataframe… how nice that would be. In fact it is very easy, as Pandas supports it natively. I can pass a SQL query directly to Pandas using the read_sql_query and that shall return to me a nice Pandas dataframe. This is done as follows:
|
1 2 3 4 5 6 7 8 9 10 |
# SQL to pandas dataframe! import sqlite3 import pandas as pd db=sqlite3.connect("EM.db") extracted = pd.read_sql_query("select * from extracted;", db) db.close() # The first 10 rows... extracted[0:10] |
That’s all for the moment, in the next articles I am going to play with jupyter on Android, with the “pickles” and with the radar or spider charts!
