Python: Read Data from SQLite via JDBC

visibility 1,414 access_time 2 years ago languageEnglish timeline Stats
timeline Stats
Page index 1.81

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:

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.

info Last modified by Raymond 2 years ago copyright This page is subject to Site terms.

Please log in or register to comment.

account_circle Log in person_add Register

Log in with external accounts

More from Kontext
Call SQL Server Stored Procedure in Python
visibility 729
thumb_up 0
access_time 6 months ago
Call SQL Server Stored Procedure in Python
Connect to SQL Server via JayDeBeApi in Python
visibility 8,792
thumb_up 1
access_time 2 years ago