Python sqlite3 create database example

Python sqlite3 create database example

Here is a detailed example of creating a SQLite database using the sqlite3 module in Python:

Step 1: Import the sqlite3 module

The first step is to import the sqlite3 module, which allows you to interact with the SQLite database.

import sqlite3

Step 2: Connect to the database

Next, you need to connect to the database. This can be done by calling the connect() function and passing in the name of the database file you want to create or use. If the file does not exist, SQLite will create it for you.

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

In this example, we are creating a database file called "example.db". You can change this to any filename you like.

Step 3: Create a cursor object

Once you have connected to the database, you need to create a cursor object. The cursor is used to execute SQL commands on the database.

cursor = conn.cursor()

Step 4: Create tables

Now that we have our cursor, we can start creating tables in our database. In this example, we will create two tables: "users" and "orders".

cursor.execute('''
CREATE TABLE users (
id INTEGER PRIMARY KEY,
name TEXT NOT NULL,
email TEXT NOT NULL UNIQUE
);
''')
cursor.execute('''
CREATE TABLE orders (
id INTEGER PRIMARY KEY,
user_id INTEGER NOT NULL,
order_date DATE NOT NULL,
FOREIGN KEY (user_id) REFERENCES users(id)
);
''')

In these examples, we are creating two tables: "users" and "orders". The "users" table has three columns: "id", "name", and "email". The "orders" table also has three columns: "id", "user_id", and "order_date".

Step 5: Insert data

Now that our tables are created, we can start inserting data into them. This is done using the execute() method with an INSERT statement.

cursor.execute("INSERT INTO users VALUES (1, 'John Doe', '[email protected]')")
conn.commit()
cursor.execute("INSERT INTO orders VALUES (1, 1, '2022-01-01')")
for i in range(10):
cursor.execute(f"INSERT INTO orders VALUES ({i+2}, 1, '2022-{0:02}-01')".format(i))
conn.commit()

In this example, we are inserting two rows into the "users" table and four rows into the "orders" table.

Step 6: Close the connection

Finally, we need to close our database connection using the close() method.

conn.close()

And that's it! We have now created a SQLite database with two tables and inserted data into those tables using Python and the sqlite3 module.

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.