AppDividend
Latest Code Tutorials

How to Create SQLite Database in Python

0

SQLite is a Relation Database Management System that is lightweight and easy to setup. SQLite is serverless which is its biggest advantage. It does not require a server to run a database, unlike other RDMS like MySQL or PostgreSQL. So we don’t need any installation setup.

SQLite databases are stored locally with files stored in the disk. This makes accessing and managing the data in the database is remarkably fast.

All transactions in SQLite are ACID compliant.

Create an SQLite Database in Python

To create an SQLite Database in Python, use the sqlite3 inbuilt module. The sqlite3 module provides an API through which you can create the database. It is compliant with Python Database API. It does not require any external libraries.

Let’s create an SQLite Database in Python.

Step 1: Import sqlite3 package

The first step is to import the sqlite3 package.

# app.py

import sqlite3

It provides an API that will be needed to create a database.

Step 2: Use connect() function

Use the connect() function of sqlite3 to create a database. It will create a connection object.

connection = sqlite3.connect('shows.db')

Our database name is “shows.db”. We saved the connection to the connection object.

Next time when we run this app.py file, it just connects to the database, and if the database is not there, then it will create one.

Step 3: Create a database table

To create a table in the relation database, we need to use the cursor object. To create a cursor object, use a connection.cursor() method.

cursor = connection.cursor()

With this cursor object, we can now execute the commands and queries on the database.

Our first command is to create a Shows table.

Use cursor.execute() method to write the CREATE TABLE query within triple commas.

cursor.execute('''CREATE TABLE IF NOT EXISTS Shows
              (Title TEXT, Director TEXT, Year INT)''')

In this code, we have written the command that will create the table with its column names and datatypes.

Step 4: Commit these changes to the database.

To commit the changes in the database, use a connection.commit() method.

connection.commit()

Step 5: Close the connection.

The last step is to close the connection using the connection.close() function.

connection.close()

That is it. See the following complete code to Create an SQLite Database in Python.

# app.py

import sqlite3

connection = sqlite3.connect('shows.db')
cursor = connection.cursor()
cursor.execute('''CREATE TABLE IF NOT EXISTS Shows
              (Title TEXT, Director TEXT, Year INT)''')

connection.commit()
connection.close()

Now the only step remaining is to run this app.py fileType the following command in your terminal.

python3 app.py

After running the file, you will see that in your current project directory, one file is created called shows.db. This is the SQLite database file generated by Python.

Conclusion

You can see that connecting Python with the SQLite database is very easy and manipulating data from Python code is also easy. You need to have a strong grasp on SQL, and that is all you need to work with Relational Database. That is it for Python with the SQLite database.

Leave A Reply

Your email address will not be published.

This site uses Akismet to reduce spam. Learn how your comment data is processed.