Python has excellent database support built into its standard library, so you can create and interact with a database without relying on external frameworks like the Django ORM.
SQLite is lightweight and easy to integrate with Python. Discover the basic principles of database programming in Python with a simple user registration app.
How to Create a Database in Python
You can find the code used for this tutorial in this GitHub repository
To create and interact with a database in Python, you need two main things: a connection and a cursor.
A connection helps you connect to an existing database or create a new one. Here's how to create a database connection in Python with SQLite:
import sqlite3
# Connect to a (new) database
conn = sqlite3.connect('path/to/database.db')
# Close the connection
conn.close()
The connect() method takes in the path to an existing database. If there is no database at the specified path, it will create one. You should close your database connection when you're done interacting with the database.
A cursor helps you interact with the connected database. You will use a cursor to execute SQL queries within your Python program. Here's how to create a cursor:
cursor = conn.cursor()
# Close the cursor
cursor.close()
You can create a cursor by calling the cursor() method on an open connection object.
How to Execute a Database Transaction in Python
Using a cursor, you can run SQL statements, queries, or scripts, to read or write data, or alter the database structure.
There are three main methods you can use to execute a database transaction.
- Cursor.execute. This method will run a single SQL statement. Here's how you use it:
cursor.execute("""
CREATE TABLE IF NOT EXISTS users (
name TEXT,
age INTEGER
)
""") - Cursor.executemany. This method lets you run the same SQL statement more than once, with different parameters each time. It takes two arguments: the SQL statement and an iterable. A good use for it is to insert several objects into the database at once:
data = [
('Alice', 25),
('Bob', 30),
('Charlie', 22)
]
cursor.executemany("""INSERT INTO users (name, age) VALUES (?, ?)""", data)Note the ? placeholders in the SQL statement. The executemany method will replace these with the corresponding values for each object.
- Cursor.executescript. As the name suggests, this method will execute an SQL script for you. You can write your SQL statements in a different file and run them with the executescript method:
with open("path/to/script.sql") as file:
sql_script = file.read()
cursor.executescript(sql_script)
How to Build a Registration App With Python and SQLite3
The logic behind a registration app involves getting the user's information with Python and storing them in a database. These steps will show you how to create a simple registration system with Python and SQLite3.
Step 1: Connect to an Existing Database or Create a New One
Start by creating a database for your app or connecting to an existing one:
import sqlite3
conn = sqlite3.connect('database.db')
cursor = conn.cursor()
# your app's code goes here
cursor.close()
conn.close()
The code above creates a connection object and a cursor to interact with the connected database.
Step 2: Create a Table for Users
You need a table to store the data users will provide when registering. Here's how to create one with your cursor:
cursor.execute("""
CREATE TABLE IF NOT EXISTS users (
first_name TEXT,
last_name TEXT,
email TEXT UNIQUE,
password TEXT
)
""")
conn.commit()
This code will create a table called users if it doesn't exist in your database. It creates four columns in the table to hold user information. The email field is unique to prevent users from creating multiple accounts with the same email.
The call to conn.commit is important to commit the query into the database. Without it, there will be no changes to the database.
If you use the executescript method, you can add the COMMIT keyword at the end of your SQL file, so you don't have to call conn.commit.
Step 3: Collect User Data
Python functions make it easy to reuse code, so it's a good idea to create a function to handle the registration feature. This function collects the user's first name, last name, email, and password.
def register_user():
first_name = input("Enter your first name: ")
last_name = input("Enter your last name: ")
email = input("Enter your email: ")
password1 = input("Enter your password: ")
password2 = input("Confirm your password: ")
Step 4: Check Password Correctness
Modify the register_user function to ensure the user enters the same password twice. If they don't you should prompt them to re-enter the password. You can achieve that with a loop like this:
def register_user():
first_name = input("Enter your first name: ")
last_name = input("Enter your last name: ")
email = input("Enter your email: ")
while True:
password1 = input("Enter your password: ")
password2 = input("Confirm your password: ")
# Check password correctness
if password1 == password2:
print("You have successfully registered!")
break
else:
print("Your passwords must match")
With this change, a user cannot register unless their passwords match.
Step 5: Check Email Uniqueness
The SQL statement that creates the users table defines the email field as unique. This means the database will return an error if a user signs up with an email that already exists. To act appropriately, you need to handle the Python exception:
def register_user():
first_name = input("Enter your first name: ")
last_name = input("Enter your last name: ")
while True:
email = input("Enter your email: ")
password1 = input("Enter your password: ")
password2 = input("Confirm your password: ")
# Check password correctness
if password1 == password2:
try:
print("You have successfully created an account.")
break
except sqlite3.IntegrityError:
print("Error: This email is already registered.")
else:
print("Your passwords must match.")
This code uses the try-except block to handle the error that will occur from duplicate emails. If the database throws an IntegrityError, the while loop will continue, prompting the user to enter a different email address.
For this sample app, it's safe to assume that an IntegrityError will only occur as a result of a duplicate email address. In a real app, you will probably use more advanced error-handling to cater for other problems that might occur.
Step 6: Insert the User's Data Into the Database
Now that you've collected and verified the user's data, it's time to add it to the database. You can use an SQL query to do so. Modify your try-except block like this:
try:
cursor.execute("""
INSERT INTO users (first_name, last_name, email, password)
VALUES (?, ?, ?, ?)
""", (first_name, last_name, email, password2))
conn.commit()
print("You have successfully created an account.")
break
except sqlite3.IntegrityError:
print("Error: This email is already registered.")
In the modified try-except block, the cursor executes an SQL insert operation. Finally, the conn.commit method commits the SQL operation to the database.
If you followed all the steps above, you should have an application that registers users and saves them to the database. You can use an app like DB Browser for SQLite to view the contents of your database:
Using Databases Instead of Collection Types
For simple databases, you may find it easier to roll your own code. However, as your application grows and your database becomes more complex, consider using a tool like Django ORM to simplify the task.
To continue practicing your low-level database skills, try implementing a login system to complement the registration program.