Python: Read Data from SQLite via JDBC

access_time 7 months ago visibility232 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.

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 Administrator at 3 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

Want to publish your article on Kontext?

Learn more

Kontext Column

Created for everyone to publish data, programming and cloud related articles.
Follow three steps to create your columns.


Learn more arrow_forward

More from Kontext

local_offer python local_offer spark local_offer pyspark local_offer spark-dataframe

visibility 26785
thumb_up 0
access_time 2 years ago

In Spark, SparkContext.parallelize function can be used to convert Python list to RDD and then RDD can be converted to DataFrame object. The following sample code is based on Spark 2.x. In this page, I am going to show you how to convert the following list to a data frame: data = [('Category A' ...

local_offer sqlite local_offer entity-framework local_offer dotnetcore

visibility 30548
thumb_up 2
access_time 3 years ago

SQLite is a self-contained and embedded SQL database engine. In .NET Core, Entity Framework Core provides APIs to work with SQLite. This page provides sample code to create a SQLite database using package Microsoft.EntityFrameworkCore.Sqlite . Create a .NET Core 2.x console application in ...

local_offer python local_offer sqlite local_offer python-database

visibility 107
thumb_up 0
access_time 7 months ago

SQLite is one of the most commonly used embedded file databases. All the mainstream programming language/framework provides APIs to interact with SQLite database. In my previous article  SQLite in .NET Core with Entity Framework Core , code snippet is provided to interact with SQLite via ...

About column