How to create table in SQLite database using python

You want to store data permanently and want to store that data in table form. Among all choices, the SQLite database is a good option for you. You can create an SQLite database with python. Python provides sqlite3 module as an interface for SQLite databases.

In this tutorial, I will show you how you can create a table in SQLite database and store data in it using the Python Programming language.

What is SQLite Database?

SQLite is a C library that provides a lightweight disk-based database that doesn’t require a separate server process and allows accessing the database using a nonstandard variant of the SQL query language.

Benefits of SQLite database in Python:

If you are working on a small project and you think that do not need any indexing and all the heavy stuff, then SQLite database is the best option to work with. Python also provides Different Python Libraries for File Manipulation. You can also store data in a file but it is the classic way of storing data.

Following are some of the upsides of using the SQLite database:

• It is a lightweight database
• It does not require any separate server
• It can be used for internal data storage
• It is can be used as a prototype

Create SQLite database in Python

Before creating the table and querying it. We need to first create our database and then create a connection to that database using the python sqlite3 module.

To create in SQLite database:

• import sqlite3 module
• use the sqlite3.connect('dbname') function
• It will create a database file with the name specified as a parameter

Use the following Python code to create an SQLite database:

import sqlite3
# it will create a databse with name sqlite.db
connection= sqlite3.connect('sqlite.db')   

A file with the name ‘sqlite.db’ is created after running the above code.

Create a Table in SQLite database using python

Once we create the connection. We need a cursor that will help us to execute SQL commands using python. We can create a cursor using the connection.cursor() function. The cursor() function is called on the connection object.

Use the following steps to create a table in SQLite database using Python

• import the sqlite3 module using import sqlite3
• call the connection=sqlite3.connect(dbname) method
• create the cursor using cursor =connection.cursor() method
• execute the SQL query to create the table using cursor.execute(query)
• commit the changes using commit() method
• close the SQLite database connection using the close() function

Python code to create a table in SQLite database:

# it will create a databse with name sqlite.db
connection= sqlite3.connect('sqlite.db')

cursor = connection.cursor()

# table name = Website 
# Table fields are 
# Post: Text type
# Autor: Text type
# Views: Real type

cursor.execute('''CREATE TABLE Website
               (Post text, Autor text, Views real)''')

# Save (commit) the changes
connection.commit()

# close connection

Insert Data into SQLite Database using Python

Once the database table is created you can insert data into a table using the insert command in SQL. Create the insert command and then execute it using the execute() function.

Inserting data into the SQLite database using Python:

import sqlite3
connection= sqlite3.connect('sqlite.db')
cursor = connection.cursor()
cursor.execute('''CREATE TABLE if not Exists Website
               (Post text, Autor text, Views real)''')

post_text = 'this is a raw  post'
post_author = 'alixapordev'
post_views = 6900

INSERT_QUERY = f"INSERT INTO Website VALUES ('{post_text}','{post_author}','{post_views}')"
cursor.execute(INSERT_QUERY)
# Save (commit) the changes
connection.commit()
# close connection
connection.close() 

The above python program will insert data into the ‘Website’ Table present in the database.

Check the inserted data in the database table using Python

The SQL ‘SELECT’ statement can help us find the inserted data in the SQLite database. The following code will return the data present in the ‘Website’ Table in the SQLite database.

import sqlite3
connection= sqlite3.connect('sqlite.db')
cursor = connection.cursor()
cursor.execute('''CREATE TABLE if not Exists Website
               (Post text, Autor text, Views real)''')

post_text = 'this is a raw  post'
post_author = 'alixapordev'
post_views = 6900

INSERT_QUERY = f"INSERT INTO Website VALUES ('{post_text}','{post_author}','{post_views}')"
cursor.execute(INSERT_QUERY)

# check the data in website using select statement
for row in cursor.execute('SELECT * FROM Website'):
    print(row)
    
# Save (commit) the changes
connection.commit()
# close connection
connection.close()

Short Summary

In this article, we have learned how to create a table in SQLite database using the python programming language. I hope this article was helpful, please leave your questions in the comment section. I will be happy to help.

Happy Coding!