Python Sqlite3 CRUD with Pandas: A simple Tutorial

 Python is a popular programming language known for its simplicity and versatility. One of its most commonly used modules is sqlite3, which allows developers to work with SQLite databases. SQLite is a lightweight database management system that is widely used for small-scale applications.

In this article, we will explore how to use the sqlite3 module in Python and pass query results to a Pandas dataframe. We will start with a basic program that creates a table, inserts some data, updates the data, deletes a record, and then retrieves the data.

The first step is to import the necessary modules: sqlite3 and pandas. We also need to create a connection object to connect to the SQLite database.

1 2 3 4

import sqlite3 import pandas as pd conn = sqlite3.connect('msgDb.db')

We then create a cursor object, which we will use to execute SQL commands. We define a table name and create a table if it doesn't exist.

1 2 3 4

c = conn.cursor() table_name = 'msgs' sql = 'create table if not exists ' + table_name + ' (id integer, msg char, status char)' c.execute(sql)

Next, we insert a record into the table using an INSERT command. We then commit the changes to the database.

1 2 3

conn.execute("INSERT INTO msgs (id, msg, status) " "VALUES (2, 'FRIEND--john--dan--hi', 'SENT')") conn.commit()

We retrieve all records from the table using a SELECT command and print the results to the console.

1 2	cursor = conn.execute("SELECT * from msgs") print(cursor.fetchall())

We then update a record in the table using an UPDATE command and commit the changes.

1 2	conn.execute("UPDATE msgs set status = 'DELV' where ID = 1") conn.commit()

We again retrieve all records from the table and print the results to the console.

1 2	cursor = conn.execute("SELECT * from msgs") print(cursor.fetchall())

We then delete a record from the table using a DELETE command and commit the changes.

1 2	conn.execute("DELETE from msgs where ID = 2;") conn.commit()

Finally, we retrieve all records from the table using a SELECT command and pass the query result to a Pandas dataframe. We print the first few records and the number of records in the dataframe to the console.

1 2 3

df = pd.read_sql_query("SELECT * from msgs", conn) print(df.head()) print(len(df))

This program demonstrates some of the basic commands used in sqlite3 and how to pass query results to a Pandas dataframe. However, this is just the tip of the iceberg. sqlite3 has many more advanced features that allow you to manage and manipulate data in more complex ways.

In conclusion, sqlite3 is a powerful and easy-to-use module that allows Python developers to work with SQLite databases. When combined with Pandas, it becomes even more powerful, allowing you to perform advanced data analysis and manipulation tasks. If you are working with small-scale applications or just starting out with databases in Python, sqlite3 is an excellent place to start.

Here is the complete code:

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36

import sqlite3 import pandas as pd conn = sqlite3.connect('msgDb.db') c = conn.cursor() table_name = 'msgs' sql = 'create table if not exists ' + table_name + ' (id integer, msg char, status char)' c.execute(sql) conn.execute("INSERT INTO msgs (id, msg, status) " "VALUES (2, 'FRIEND--john--dan--hi', 'SENT')") conn.commit() cursor = conn.execute("SELECT * from msgs") print(cursor.fetchall()) conn.execute("UPDATE msgs set status = 'DELV' where ID = 1") conn.commit() cursor = conn.execute("SELECT * from msgs") print(cursor.fetchall()) conn.execute("DELETE from msgs where ID = 2;") conn.commit() cursor = conn.execute("SELECT * from msgs") print(cursor.fetchall()) df = pd.read_sql_query("SELECT * from msgs", conn) print(df.head()) print(len(df)) )

I would also use this Sqlite3 database to my chat application project which will serve as the back end database of client application