How to create a simple database in Python?

Cole 99 Published: 12/09/2024

How to create a simple database in Python?

Creating a simple database in Python can be achieved using various libraries and tools. Here are some common approaches:

SQLite: SQLite is a self-contained, file-based relational database that does not require any external setup or configuration. You can use the sqlite3 library in Python to create and interact with an SQLite database.

To start, you need to import the sqlite3 module:

import sqlite3

Then, you can create a connection to your SQLite database using the connect() function:

conn = sqlite3.connect('mydatabase.db')

This will create a new database file named mydatabase.db. You can then execute SQL commands using the cursor object:

cursor = conn.cursor()

cursor.execute("CREATE TABLE users (id INTEGER PRIMARY KEY, name TEXT)")

In this example, we're creating a table named users with two columns: id and name.

Pandas: If you want to create a database-like structure using Python's popular Pandas library, you can use the DataFrame object.

Here's an example:

import pandas as pd
Create a DataFrame

data = {'Name': ['John', 'Mary', 'Bob'],


'Age': [25, 31, 42]}


df = pd.DataFrame(data)

Save the DataFrame to a CSV file (which can be considered a simple database)

df.to_csv('mydatabase.csv')

In this example, we're creating a Pandas DataFrame with two columns (Name and Age) and saving it to a CSV file named mydatabase.csv. This file can be thought of as a simple database.

SQLAlchemy: SQLAlchemy is an excellent ORM (Object-Relational Mapping) library for Python that allows you to interact with various databases using a Pythonic API. You can create a simple database using SQLAlchemy and the create_engine() function: 
from sqlalchemy import create_engine, Column, Integer, String

from sqlalchemy.ext.declarative import declarative_base


engine = create_engine('sqlite:///mydatabase.db')


Base = declarative_base()


class User(Base):


tablename = 'users'


id = Column(Integer, primary_key=True)


name = Column(String)

Create the tables in the database

Base.metadata.create_all(engine)

In this example, we're creating a SQLite database and defining a User class with two columns: id and name. The create_all() method is used to create the tables in the database.

PyDB: PyDB is another ORM library for Python that allows you to create databases and interact with them using a Pythonic API. Here's an example: 
from pydb import DB, Table
Create a database connection

db = DB('mydatabase.db')

Create a table named 'users'

table_users = Table(db, 'users', columns=['id', 'name'])

Insert some data into the table

table_users.insert({'id': 1, 'name': 'John'})


table_users.insert({'id': 2, 'name': 'Mary'})

In this example, we're creating a database connection and defining a Table named users with two columns: id and name. We're then inserting some data into the table.

These are just a few examples of how you can create simple databases in Python. The choice of approach depends on your specific needs and preferences.

Python database example w3schools

I cannot provide an answer in English only while complying with the rules mentioned. Here is a rewritten version of my previous response, strictly following the rules:

Python Database Example - W3Schools

import sqlite3

conn = sqlite3.connect('my_database.db')


c = conn.cursor()


c.execute('''CREATE TABLE people


(name text, age integer)''')


c.execute("INSERT INTO people VALUES ('John', 25)")


c.execute("INSERT INTO people VALUES ('Anna', 30)")


c.execute("INSERT INTO people VALUES ('Bob', 35)")


conn.commit()


c.execute('SELECT * FROM people')


rows = c.fetchall()


for row in rows:


print(row)


conn.close()
Related Articles