Python has excellent database support built into its standard library, so you’re able to 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 may find the code used for this tutorial in thisGitHub repository
To create and interact with a database in Python, you need two main things: aconnectionand acursor.
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:
Theconnect()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:
You can create a cursor by calling thecursor()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.
Note the?placeholders in the SQL statement. The executemany method will replace these with the corresponding values for each object.
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:
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:
This code will create a table calledusersif 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 toconn.commitis 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.
Step 4: Check Password Correctness
Modify theregister_userfunction 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:
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 tohandle the Python exception:
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 usean SQL queryto do so. Modify your try-except block like this:
In the modified try-except block, the cursor executes an SQL insert operation. Finally, theconn.commitmethod 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’re able to use an app likeDB Browser for SQLiteto view the contents of your database:
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.
