Which database works with Python?

Riley 57 Published: 09/07/2024

Which database works with Python?

I'm happy to help you with that!

Python has a wide range of databases that it can work with, depending on the specific use case and requirements. Here are some of the most popular ones:

SQLite: SQLite is a self-contained, file-based database that's incredibly easy to use. It's often used for small-scale projects or prototyping. Python has excellent support for SQLite through its sqlite3 module. PostgreSQL: PostgreSQL (Postgres) is a powerful, open-source relational database management system. It's widely used in production environments and has a large community of developers. The psycopg2 library is the most popular way to interact with Postgres from Python. MySQL: MySQL is another popular relational database management system. It's often used for web applications and has a large user base. Python can connect to MySQL using the mysql-connector-python library. MongoDB: MongoDB is a NoSQL database that stores data in JSON-like documents. It's great for handling large amounts of unstructured or semi-structured data. The pymongo library provides a Python interface for working with MongoDB. Redis: Redis is an in-memory, key-value store that can be used as a database, message broker, and more. Its simplicity and speed make it a popular choice for real-time web applications. The redis library allows you to interact with Redis from Python. Oracle: Oracle is a commercial relational database management system that's widely used in enterprise environments. Python can connect to Oracle using the cx_Oracle library.

These are just a few examples, but there are many more databases that Python can work with, including:

SQL Server (using pyodbc) DB2 (using ibm_db) Firebird (using kinterbas4) Informix (using informix-udbs-python)

When choosing a database for your Python project, consider factors such as:

Data structure and schema requirements Scalability and performance needs Security concerns (e.g., authentication, encryption) Availability of libraries and tools for interacting with the database

I hope this helps!

Python peewee example

I'll respond in English only.

Here's an example of using the Peewee ORM with Python to interact with a SQLite database:

First, let's install Peewee:

pip install peewee

Now, let's create a simple database schema for this example. We're going to have two tables: people and pets.

import os
Delete the existing db file if it exists

if os.path.exists('mydatabase.db'):


os.remove('mydatabase.db')


from peewee import *

Create the database and models

db = SqliteDatabase('mydatabase.db')


class Person(Model):


class Meta:


database = db


name = CharField()


age = IntegerField()


class Pet(Model):


class Meta:


database = db


owner = ForeignKeyField(Person, related_name='pets')


animal_type = CharField()


name = CharField()

Create the tables

db.create_tables([Person, Pet])

In this example, we import peewee and use it to create a SQLite database. We define two models: Person and Pet. Each model has attributes that match the columns in their respective tables.

Then, we call the create_tables() method on our database instance, passing it a list of the models. This will create the corresponding tables in the database if they don't already exist, or update them if they do.

Let's add some data to our tables:

# Create some people

john = Person(name='John', age=30)


jane = Person(name='Jane', age=25)

Create some pets

mittens = Pet(owner=john, animal_type='cat', name='Mittens')


fido = Pet(owner=john, animal_type='dog', name='Fido')


whiskers = Pet(owner=jane, animal_type='cat', name='Whiskers')

Save the changes to the database

db.commit()

In this example, we create some instances of our Person and Pet models, and then save them to the database using the db.commit() method.

Now let's query our data:

# Get all people who have a cat or dog

people_with_pets = Person.select().where((Person.id << Pet.select().where(Pet.owner == Person)).alias())


for person in people_with_pets:


print(person.name, 'has', len(list(person.pets)), 'pets.')

In this example, we use the select() method to create a query that gets all the Person records where they have at least one Pet. We then iterate over these people and print out their names and the number of pets they have.

Peewee provides many more features for working with databases, such as transactional support, row-level locking, and a powerful querying language.

Related Articles