Python: Read Data from SQLite via JDBC

access_time 10 months ago visibility441 comment 0

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.


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",
curs = conn.cursor()
curs.execute("select * from Customer")
records = curs.fetchall()
df = pd.DataFrame(records)

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 .\
   0             1   2
0  1    Customer 1  30
1  2  Customer Two  20


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 months ago copyright This page is subject to Site terms.
Like this article?
Share on

Please log in or register to comment.

account_circle Log in person_add Register

Log in with external accounts

More from Kontext

visibility 1669
thumb_up 0
access_time 10 months ago

Python JayDeBeApi module allows you to connect from Python to Teradata databases using Java JDBC drivers. In article Connect to Teradata database through Python , I showed how to use teradata package to connect to Teradata via Teradata ODBC driver. This article demos how to use this JayDeBeApi ...

visibility 32
thumb_up 0
access_time 1 month ago

JDK (Java Development Kit) is required for many big data frameworks such as Hadoop, Spark, Hive, etc.  Follow these steps to install JDK SE 11 on macOS. The steps were tested on Big Sur. Go to JDK 11 download page:  Java SE Development Kit 11- - Downloads ( . Download ...

visibility 7
thumb_up 0
access_time 2 days ago

To connect to MySQL in Python, there are various approaches: JDBC driver - JayDeBeApi python wrapper package. Native python MySQL packages, for example, MySQL connector, etc.  This article provides an example of using MySQL connector package to connect to MySQL. Python 3 MySQL server.