Pandas Read from SQLite Database

access_time 8 months ago visibility1711 comment 0

In my previous posts, I showed how to use jaydebeapi or sqlite3 packages to read data from SQLite databases. The high level approach I followed are:

  • Create database connection
  • Create a cursor object via executing SQL SELECT command.
  • Fetch all the records via the cursor
  • Convert the returned list of records a pandas DataFrame object.

In fact, pandas framework provides APIs to directly read data from SQLite or other SQL databases. We just need to create a DBI connection. In fact, we both connections created via JDBC or sqlite3 can be directly used.

The following code snippets show you how to do that.

Create Pandas DataFrame using JayDeBeApi 

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")
df = pd.read_sql("select * from sqlite_master", con=conn)
print(df)
conn.close()

The above code snippet use pandas.read_sql API to read data directly as a pandas dataframe.

The output looks like the following:

 python .\pandas-sqlite.py
    type      name  tbl_name  rootpage                                                sql
0  table  Customer  Customer         2  CREATE TABLE Customer (ID int, Name text, Age ...

Create Pandas DataFrame using sqlite3

Alternatively, we can also use the same function on the connection object created via sqlite3 API.

import sqlite3
import pandas as pd
database = "../example.sqlite"

conn = sqlite3.connect(database)

df = pd.read_sql("select * from sqlite_master", con=conn)
print(df)
conn.close()

The outputs are exactly the same as the previous one.

Write data into SQLite database

We can use pandas.dataframe.to_sql function to write dataframe data into a table in SQLite or any other SQL databases such as Oracle, SQL Server, MySQL, Teradata, etc.

import sqlite3
import pandas as pd
database = "../example.sqlite"

conn = sqlite3.connect(database)
users = {'ID': [1, 2, 3], 'Value': ['A', 'B', 'C']}
df = pd.DataFrame(users, columns=['ID', 'Value'])
print(df)
df.to_sql(name='Users', con=conn)
df = pd.read_sql("select * from sqlite_master", con=conn)
print(df)
conn.close()
The above code snippets creates a dataframe and then save it as table Users in the example.sqlite database.
Output:
python .\pandas-sqlite-sqlite3.py
   ID Value
0   1     A
1   2     B
2   3     C
    type            name  tbl_name  rootpage                                                sql
0  table        Customer  Customer         2  CREATE TABLE Customer (ID int, Name text, Age ...
1  table           Users     Users         3  CREATE TABLE "Users" (\n"index" INTEGER,\n  "I...
2  index  ix_Users_index     Users         4  CREATE INDEX "ix_Users_index"ON "Users" ("index")

As you can see, the table is created and also an index is created too. Read through the official documentation about why an index column is created. Let's me know if you cannot figure out the reason.

info Last modified by Administrator at 4 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

Pandas DataFrame Plot - Pie Chart

local_offer plot local_offer pandas local_offer jupyter-notebook local_offer python local_offer pandas-plot

visibility 6309
thumb_up 0
access_time 8 months ago

This article provides examples about plotting pie chart using  pandas.DataFrame.plot  function. The data I'm going to use is the same as the other article  Pandas DataFrame Plot - Bar Chart . I'm also using Jupyter Notebook to plot them. The DataFrame has 9 records: DATE TYPE ...

local_offer sqlite local_offer entity-framework local_offer dotnetcore

visibility 32312
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 ...

Pandas DataFrame Plot - Area Chart

local_offer plot local_offer jupyter-notebook local_offer python local_offer pandas local_offer pandas-plot

visibility 218
thumb_up 0
access_time 8 months ago

This article provides examples about plotting area chart using  pandas.DataFrame.plot  or  pandas.core.groupby.DataFrameGroupBy.plot   function. The data I'm going to use is the same as the other article  Pandas DataFrame Plot - Bar Chart . I'm also using Jupyter ...

About column