Python: Read Data from SQLite via JDBC
insights Stats
toc Table of contents
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:
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.
