Python and MySQL are two of the most powerful tools for web development. Python is lauded for its simplicity and versatility, while MySQL is an open-source relational database management system that is widely used for web-based applications. When used together, they can be a formidable pair for creating dynamic and interactive web applications. In this article, we will explore how to use Python with MySQL for web development, focusing on creating a separate database configuration and connection file.
Prerequisites
Before we begin, you will need:
- Python installed on your machine. If it’s not already installed, you can download it from the official Python website.
- MySQL Server installed on your machine. You can download it from the official MySQL website.
- The Python MySQL Connector library. This can be installed via pip by typing `pip install mysql-connector-python` in your terminal or command prompt.
- A basic understanding of Python and MySQL syntax.
1. Setting Up MySQL
First, you need to create a database and a table in your MySQL server. Let’s assume we’re creating a database named webdb and a table named users.
1 2 3 4 5 6 7 8 9 10 | CREATE DATABASE webdb; USE webdb; CREATE TABLE users( id INT AUTO_INCREMENT PRIMARY KEY, name VARCHAR(50), email VARCHAR(50), password VARCHAR(50) ); |
2. Creating a Database Configuration File
For improved security and modularity, it’s always a good practice to keep database configuration in a separate file. Let’s create a db_config.py file:
1 2 3 4 5 6 7 8 | # Define a dictionary with the database configuration details db_config = { 'user': 'your_username', # Your MySQL username 'password': 'your_password', # Your MySQL password 'host': 'localhost', # Your MySQL server host, typically 'localhost' 'database': 'webdb', # The database you want to connect to 'raise_on_warnings': True # Whether to raise an exception on warnings } |
Remember to replace ‘your_username’ and ‘your_password’ with your actual MySQL username and password.
3. Creating a Database Connection File
Next, let’s create a db_connection.py file where we will establish a connection with our MySQL database using the configuration we defined above:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 | import mysql.connector from db_config import db_config def create_conn(): conn = None try: # Establish a connection to the database using the configuration details conn = mysql.connector.connect(**db_config) # If the connection is successful, print a success message if conn.is_connected(): print('Connected to MySQL database') # Sample output: 'Connected to MySQL database' except Error as e: print(e) return conn |
In this file, we define a create_conn() function that establishes a connection to the database using mysql.connector.connect(**db_config). The `**db_config` syntax means we’re passing the items in the db_config dictionary as keyword arguments to the `connect()` function.
4. Interacting with the Database
With our configuration and connection setup ready, we can now interact with our database. Let’s assume we want to add a user to our users table. We can create a new Python file, say app.py, and write the following code:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 | from db_connection import create_conn def add_user(name, email, password): # Establish a connection to the database conn = create_conn() # If the connection is successful... if conn is not None: # Create a cursor object cursor = conn.cursor() # Define an SQL query and values query = "INSERT INTO users (name, email, password) VALUES (%s, %s, %s)" values = (name, email, password) # Execute the SQL query with the values cursor.execute(query, values) # Commit the transaction conn.commit() # Print a success message print(f"User {name} added successfully") # Sample output: 'User John Doe added successfully' # Close the cursor and connection cursor.close() conn.close() add_user("John Doe", "john@example.com", "password123") |
In this code, we’re importing our create_conn() function from the db_connection.py file. The add_user() function takes three parameters: `name, email, and password. We then call create_conn() to establish a connection with the database. If the connection is successful, we create a cursor object and execute an INSERT SQL query to add a new user to the users table. After executing the query, we commit the transaction, print a success message, and close the cursor and connection.
Similarly, you can define other functions to interact with your database, such as retrieving, updating, or deleting data.
5. Fetching Data
To fetch data from the database, you can use the SELECT statement. Let’s create a function fetch_users() that fetches and prints all users from the users table.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 | from db_connection import create_conn def fetch_users(): # Establish a connection to the database conn = create_conn() # If the connection is successful... if conn is not None: # Create a cursor object cursor = conn.cursor() # Execute a SELECT SQL statement cursor.execute("SELECT * FROM users") # Fetch all rows returned by the query rows = cursor.fetchall() # Print each row for row in rows: print(row) # Sample output: (1, 'John Doe', 'john@example.com', 'password123') # Close the cursor and connection cursor.close() conn.close() fetch_users() |
In this code, cursor.execute() is used to execute a SELECT SQL statement. cursor.fetchall() is then used to fetch all rows returned by the query. The rows are then printed one by one.
6. Updating Data
To update data in the database, you can use the UPDATE statement. Let’s create a function update_user() that updates the email of a user.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 | from db_connection import create_conn def update_user(id, new_email): # Establish a connection to the database conn = create_conn() # If the connection is successful... if conn is not None: # Create a cursor object cursor = conn.cursor() # Define an SQL query and values query = "UPDATE users SET email = %s WHERE id = %s" values = (new_email, id) # Execute the SQL query with the values cursor.execute(query, values) # Commit the transaction conn.commit() # Print a success message print(f"User {id}'s email updated successfully") # Sample output: ""User 1's email updated successfully" # Close the cursor and connection cursor.close() conn.close() update_user(1, "newemail@example.com") |
Here, cursor.execute() is used to execute an UPDATE SQL statement, which updates the email of the user with the specified id.
7. Deleting Data
To delete data from the database, you can use the DELETE statement. Let’s create a function delete_user() that deletes a user.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 | from db_connection import create_conn def delete_user(id): # Establish a connection to the database conn = create_conn() # If the connection is successful... if conn is not None: # Create a cursor object cursor = conn.cursor() # Define an SQL query and values query = "DELETE FROM users WHERE id = %s" values = (id,) # Execute the SQL query with the values cursor.execute(query, values) # Commit the transaction conn.commit() # Print a success message print(f"User {id} deleted successfully") # Sample output: "User 1 deleted successfully" # Close the cursor and connection cursor.close() conn.close() delete_user(1) |
Here, cursor.execute() is used to execute a DELETE SQL statement, which deletes the user with the specified id.
Conclusion
Python and MySQL can work together seamlessly to create powerful and dynamic web applications. With proper setup and organization, such as creating separate configuration and connection files, your code can be more secure, modular, and easier to maintain. Always remember to close your database connections after you’re done to free up resources.