Home » SQLite » How To Create SQLite Database With Python

How To Create SQLite Database With Python

In this post, we’re going to make a Python project, where we’ll create a SQLite database. Furthermore, we’ll dive into all of the data type fields we can create using peewee package.

Peewee is a very versatile package for working with databases, since it supports many different database types. You can also work with PostgreSQL, CockroachDB and MYSQL databases.

Let’s create the SQLite database

First of all, like with any other Python project, we need to import all the necesarry libraries and tools.

import os
import peewee
from datetime import date

Next, we’ll create the database and save it locally in the same directory as the project file.

ROOT = os.path.dirname(__file__)
db = peewee.SqliteDatabase(os.path.join(ROOT, 'data.db'))

In this example, we’re going to create a database that will store information about employees of a certain company. Furthermore, we’ll store a name, start of employment date, and position for each employee. Therefore, we need to create a model that will contain different data type fields for this information.

class Employee(peewee.Model):
    name = peewee.CharField()
    employed_since = peewee.DateField()
    position = peewee.CharField()

    class Meta:
        database = db

Additionally, we can choose from a variety of different field types. Here is also a list of what fields peewee package supports for SQLite databases:

• AutoField
• BigAutoField
• IntegerField
• BigIntegerField
• SmallIntegerField
• FloatField
• DoubleField
• DecimalField
• CharField
• FixedCharField
• TextField
• BlobField
• BitField
• BigBitField
• UUIDField
• BinaryUUIDField
• DateTimeField
• DateField
• TimeField
• TimestampField
• IPField
• BooleanField
• BareField
• ForeignKeyField

As you can see, there’s a lot of options to choose from.

Alright, now we need to connect to the database and create the table for employees.

db.connect()
db.create_tables([Employee])

Lastly, we’re ready to input all the information about employees. For this example, I created a simple dictionary, containing the information about 3 employees.

employees = {
    'Debra': {
        'employed_since': date(2012, 5, 26),
        'position': 'Writer'
    },
    'Arthur': {
        'employed_since': date(2020, 3, 22),
        'position': 'Janitor'
    },
    'Mark': {
        'employed_since': date(2007, 1, 6),
        'position': 'CTO'
    }
}

for employee in employees:
    e = Employee.create(
        name=employee,
        employed_since=employees[employee]['employed_since'],
        position=employees[employee]['position']
    )
    e.save()

That’s it!

Here is also the entire code for this project.

import os
import peewee
from datetime import date

ROOT = os.path.dirname(__file__)
db = peewee.SqliteDatabase(os.path.join(ROOT, 'data.db'))

class Employee(peewee.Model):
    name = peewee.CharField()
    employed_since = peewee.DateField()
    position = peewee.CharField()

    class Meta:
        database = db

db.connect()
db.create_tables([Employee])

employees = {
    'Debra': {
        'employed_since': date(2012, 5, 26),
        'position': 'Writer'
    },
    'Arthur': {
        'employed_since': date(2020, 3, 22),
        'position': 'Janitor'
    },
    'Mark': {
        'employed_since': date(2007, 1, 6),
        'position': 'CTO'
    }
}

for employee in employees:
    e = Employee.create(
        name=employee,
        employed_since=employees[employee]['employed_since'],
        position=employees[employee]['position']
    )
    e.save()

Bonus

In case you’re working on a WSL2, you might run into a problem viewing database contents with software run on windows, such as DBeaver. If you’re working with SQLite database, like we done here in this project, you can use VS Code Extension SQLite made by alexcvzz.

Conclusion

To conclude, we made a simple Python example where we create a SQLite database and insert some employee information in it. I learned a lot while working on this project and I hope you will find it helpful as well.