Python: Read Data from SQLite via JDBC

To read data from SQLite database in Python, you can use the built-in sqlite3 package. Another approach is to use SQLite JDBC driver via JayDeBeApi python package.

Prerequisites

SQLite JDBC driver

Download the JAR file from one of the online repositories:

• Maven Repository
  
• BitBucket
• or any other equivalent location.

Save the jar file into the directory where you are going to create the Python script. 

For my case, I'm using sqlite-jdbc-3.30.1.jar.

JayDeBeApi package

Install this package it is not available in your system. Refer to the following link for more details about how to install it:

How to install JayDeBeApi

Read data from SQLite database

Use the following simple code to read data from SQLite database:

import jaydebeapi
import pandas as pd
database = "../example.sqlite"

conn = jaydebeapi.connect("org.sqlite.JDBC",
                          f"""jdbc:sqlite:{database}""",
                          None,
                          "sqlite-jdbc-3.30.1.jar")
curs = conn.cursor()
curs.execute("select * from Customer")
records = curs.fetchall()
df = pd.DataFrame(records)
print(df)
curs.close()
conn.close()

The driver class name is org.sqlite.JDBC. The database I'm using is in the parent folder. The database was created by the example in one of my previous post Data Operations with SQLite Database via Python.

As my database has no password, thus None is specified as user name and password. 

The above code snippet also converts the list returned by fetchall function to a Pandas dataframe. 

The result:

> python .\jadebeapi-sqlite.py
   0             1   2
0  1    Customer 1  30
1  2  Customer Two  20

Summary

With JayDeBeApi, it is very easy to read data from SQLite database using JDBC as you could do with other relational databases such as SQL Server, Teradata, MySQL, Postgre SQL, etc.