Import SQLite to Python

 in Python  tagged sql / import / table / select / query / python / sqlite / pandas / dataframe by

There are at least two ways to import a SQLite table into a Python program.. You can use pandas or a cursor.

pandas DataFrame

Can you import a table in a SQLite database into a pandas DataFrame in Python? Yes you can. I will use Anaconda Navigator to create a Jupyter Notebook.

In this example I have already created my SQLite database and a table in the database. I have also added four rows of data. 

import sqlite3
import pandas as pd

# Create your connection to SQLite.
conn = sqlite3.connect('D:\Test\peopleDB.db')

df = pd.read_sql_query("SELECT * FROM friends", conn)
df

Import SQLite to Python

In Jupyter Notebook you get the results of the SQL SELECT statement.

No pandas – Cursor

Let’s do things a different way. Here we will not use pandas. This is a separate and new project. Here is the code. 

import sqlite3
import pandas as pd
# Create your connection to SQLite.
conn = sqlite3.connect('D:\Test\peopleDB.db')
cursor = conn.cursor()
results = cursor.execute('''SELECT * FROM friends''')
print(cursor.fetchall())

[('Bob', 12), ('Sally', 67), ('Suzie', 33), ('Rowan', 44)]

We can do a for loop to iterate through the results. 

# let's do a for loop
results_2 = cursor.execute('''SELECT * FROM friends''')
for row in results_2:
    print(row)

('Bob', 12)
('Sally', 67)
('Suzie', 33)
('Rowan', 44)

Just for illustration, if you try to print the cursor, you get the cursor object, not the data. 

results_2 = cursor.execute('''SELECT * FROM friends''')
print(results_2)
# results_2 is just the cursor object, not the data

<sqlite3.Cursor object at 0x00000220A922B240>

Leave a Reply