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.

copyright This page is subject to Site terms.

More from Kontext

local_offer teradata local_offer python

visibility 1035
thumb_up 1
access_time 4 months ago

Pandas is commonly used by Python users to perform data operations. In many scenarios, the results need to be saved to a storage like Teradata. This article shows you how to do that easily using JayDeBeApi or  ...

open_in_new Spark + PySpark

local_offer python

visibility 317
thumb_up 0
access_time 4 months ago

CSV is a common data format used in many applications. It's also a common task for data workers to read and parse CSV and then save it into another storage such as RDBMS (Teradata, SQL Server, MySQL). In my previous article  ...

open_in_new Python Programming

local_offer teradata local_offer python local_offer Java

visibility 584
thumb_up 0
access_time 4 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 ho...

open_in_new Python Programming

local_offer hadoop local_offer hive local_offer Java

visibility 663
thumb_up 1
access_time 4 months ago

When I was configuring Hive 3.0.0 in Hadoop 3.2.1 environment, I encountered the following error: Exception in thread "main" java.lang.NoSuchMethodError: com.google.common.base.Preconditions.checkArgument(ZLjava/lang/String;Ljava/lang/Object;)V Ro...

open_in_new Hadoop

comment Comments (0)

comment Add comment

Please log in or register to comment.

account_circle Log in person_add Register

Log in with external accounts

No comments yet.

Kontext Column

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


Learn more arrow_forward