How to create a local database with Python?

Francis 109 Published: 09/19/2024

How to create a local database with Python?

Creating a local database with Python is an essential step for any project that requires data storage and retrieval. Here, we'll explore the popular options for creating a local database using Python.

Option 1: SQLite

SQLite is a self-contained, file-based SQL database that doesn't require a separate server process. You can easily create a SQLite database using Python's sqlite3 module.

Here's an example:

import sqlite3
Create a connection to the database

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

Create a cursor object

cursor = conn.cursor()

Create a table

cursor.execute('''


CREATE TABLE users (


id INTEGER PRIMARY KEY,


name TEXT NOT NULL,


email TEXT NOT NULL


)


''')

Insert some data

cursor.execute("INSERT INTO users VALUES (1, 'John Doe', '[email protected]')")


conn.commit()

Close the connection

conn.close()

Option 2: SQLAlchemy

SQLAlchemy is a popular ORM (Object-Relational Mapping) tool for Python. It provides a high-level SQL database abstraction that simplifies database interactions.

Here's an example:

from sqlalchemy import create_engine, Column, Integer, String

from sqlalchemy.ext.declarative import declarative_base


Base = declarative_base()


class User(Base):


tablename = 'users'


id = Column(Integer, primary_key=True)


name = Column(String)


email = Column(String)

Create a database engine

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

Create the table

Base.metadata.create_all(engine)

Insert some data

user = User(name='John Doe', email='[email protected]')


db_session = sessionmaker(bind=engine)()


db_session.add(user)


db_session.commit()

Close the connection

db_session.close()

Option 3: MongoDB

MongoDB is a NoSQL document-based database that's well-suited for handling large amounts of JSON-like data. You can use the pymongo library to interact with a MongoDB instance.

Here's an example:

from pymongo import MongoClient
Create a connection to the database

client = MongoClient('localhost', 27017)

Select the database and collection

db = client['mydatabase']


collection = db['users']

Insert some data

user = {'name': 'John Doe', 'email': '[email protected]'}


collection.insert_one(user)

Close the connection

client.close()

Conclusion

In this article, we've explored three popular options for creating a local database using Python: SQLite, SQLAlchemy, and MongoDB. Each option has its strengths and weaknesses, and choosing the right one depends on your project's specific requirements.

Remember to always properly close or commit your changes when working with databases to ensure data integrity and avoid potential errors.

How to create a database in sqlite3 Python?

Here's a step-by-step guide on how to create a database in SQLite using the sqlite3 Python module:

Step 1: Install the sqlite3 Module

To start, you'll need to install the sqlite3 module if you haven't already. You can do this by running the following command in your terminal:

pip install sqlite3

Step 2: Import the sqlite3 Module

Once installed, you can import the sqlite3 module into your Python script using the following code:

import sqlite3

Step 3: Create a Connection to the Database

To create a connection to a SQLite database, you'll need to use the connect() function and specify the path to the database file. For example:

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

This will create a new SQLite database file called mydatabase.db if it doesn't already exist.

Step 4: Create Tables (Optional)

If you want to create tables in your database, you can use the cursor() method to execute SQL queries. For example:

c = conn.cursor()

c.execute('''


CREATE TABLE users (


id INTEGER PRIMARY KEY,


name TEXT NOT NULL,


email TEXT UNIQUE NOT NULL


);


''')

This code creates a table called users with three columns: id, name, and email.

Step 5: Insert Data (Optional)

To insert data into your tables, you can use the executemany() method to execute multiple SQL queries at once. For example:

c.executemany('''

INSERT INTO users (name, email) VALUES (?, ?);


''', [


('John Doe', '[email protected]'),


('Jane Smith', '[email protected]'),

...

])

This code inserts multiple rows into the users table.

Step 6: Commit and Close the Connection

Finally, you'll need to commit your changes using the commit() method and close the connection using the close() method:

conn.commit()

conn.close()

That's it! You now have a SQLite database created using the sqlite3 Python module.

Here's some sample code that demonstrates how to create a database, create tables, insert data, and query the data:

import sqlite3
Create a connection to the database

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

Create a cursor object

c = conn.cursor()

Create a table called 'users'

c.execute('''


CREATE TABLE users (


id INTEGER PRIMARY KEY,


name TEXT NOT NULL,


email TEXT UNIQUE NOT NULL


);


''')

Insert some data into the 'users' table

c.executemany('''


INSERT INTO users (name, email) VALUES (?, ?);


''', [


('John Doe', '[email protected]'),


('Jane Smith', '[email protected]'),


])

Commit the changes

conn.commit()

Close the connection

conn.close()

Query the data using a SQL SELECT statement

c.execute('SELECT * FROM users;')


results = c.fetchall()


print(results)

Close the cursor and database connection

c.close()


conn.close()

This code creates a SQLite database called mydatabase.db, creates a table called users with three columns, inserts some data into the users table, commits the changes, and then queries the data using a SQL SELECT statement.

Related Articles