Database programming is a critical part of interacting with the database and managing data in your application. MySQL is a popular relational database management system (RDBMS), and MySQL database programming is relatively easy in Python.
This article introduces how to use Python for MySQL database programming, including operations such as connecting to the database, executing SQL queries, inserting, updating, and deleting data.
Directory
- Install MySQL driver
- Connect to MySQL database
- Create database
- Create data table
- Insert data
- Query data
- update data
- delete data
- Execute transaction
- Close database connection
- Example: A complete MySQL database programming example
- Security considerations
- Summarize
1. Install MySQL driver
Before you begin, you need to install the MySQL database driver for Python. One of the most commonly used drivers is mysql-connector-python
Install using pip:
pip install mysql-connector-python
2. Connect to MySQL database
Before performing any database operations, you first need to establish a connection to the MySQL database. Typically, you need to provide the database hostname, username, password, and database name.
Here is an example of connecting to a MySQL database:
import mysql.connector #Create database connection db = mysql.connector.connect( host="localhost", # MySQL server address user="username", # username password="password", # Password database="mydatabase" # Database name ) #Create a cursor object for executing SQL queries cursor = db.cursor()
3. Create database
To create a new database, you can perform the following operations:
# Create a new database named "mydatabase" cursor.execute("CREATE DATABASE mydatabase")
4. Create data table
In a database, data is organized in tabular form. Before creating a table, you need to define the structure of the table, including column names, data types, and constraints.
Here is an example:
# Create a data table named "customers" cursor.execute("CREATE TABLE customers (id INT AUTO_INCREMENT PRIMARY KEY, name VARCHAR(255), address VARCHAR(255))")
5. Insert data
Inserting data is the process of adding data to a data table. You can use SQL’s INSERT INTO
statement to perform insert operations.
Here is an example:
# Insert a record into the "customers" table sql = "INSERT INTO customers (name, address) VALUES (%s, %s)" values = ("John Smith", "123 Main St") cursor.execute(sql, values) # Submit changes to the database db.commit() print(f"{cursor.rowcount} records inserted")
6. Query data
Querying data is the process of retrieving data from a database. Use SQL’s SELECT
statement to perform query operations.
Here is an example:
# Query all records cursor.execute("SELECT * FROM customers") # Get query results results = cursor.fetchall() for row in results: print(row)
7. Update data
Updating data is the process of modifying existing data. Use SQL’s UPDATE
statement to perform update operations.
Here is an example:
# Update the record with id 1 in the "customers" table sql = "UPDATE customers SET address = %s WHERE id = %s" values = ("456 Elm St", 1) cursor.execute(sql, values) # Submit changes to the database db.commit() print(f"Updated {cursor.rowcount} records")
8. Delete data
Deleting data is the process of removing data from the database. Use SQL’s DELETE
statement to perform a delete operation.
Here is an example:
# Delete the record with id 1 in the "customers" table sql = "DELETE FROM customers WHERE id = %s" values = (1,) cursor.execute(sql, values) # Submit changes to the database db.commit() print(f"{cursor.rowcount} records deleted")
9. Execute transactions
A transaction is a set of SQL operations that either all succeed or all fail. In some cases, transactions are required to ensure database integrity.
Here is an example:
try: db.start_transaction() #Perform a series of SQL operations cursor.execute("INSERT INTO customers (name, address) VALUES (%s, %s)", ("Alice", "789 Oak St")) cursor.execute("UPDATE customers SET address = %s WHERE id = %s", ("789 Elm St", 2)) db.commit() # Submit transaction except: db.rollback() #Transaction rollback, undo previous operations
10. Close database connection
After completing the database operation, don’t forget to close the database connection to release resources.
cursor.close() # Close the cursor db.close() # Close the database connection
11. Example: A complete MySQL database programming example
The following is a complete MySQL database programming example, including connecting to the database, creating tables, inserting data, querying data, updating data and deleting data:
import mysql.connector #Create database connection db = mysql.connector.connect( host="localhost", user="username", password="password", database="mydatabase" ) #Create cursor object cursor = db.cursor() #Create a data table named "customers" cursor.execute("CREATE TABLE customers (id INT AUTO_INCREMENT PRIMARY KEY, name VARCHAR(255), address VARCHAR(255))") #Insert record sql = "INSERT INTO customers (name, address) VALUES (%s, %s)" values = [("John Smith", "123 Main St"), ("Alice Johnson", "456 Elm St")] cursor.executemany(sql, values) db.commit() # Query the records cursor.execute("SELECT * FROM customers") results = cursor.fetchall() for row in results: print(row) # update record sql = "UPDATE customers SET address = %s WHERE name = %s" values = ("789 Oak St", "Alice Johnson") cursor.execute(sql, values) db.commit() # Delete Record sql = "DELETE FROM customers WHERE name = %s" values = ("John Smith",) cursor.execute(sql, values) db.commit() # Close the cursor and database connection cursor.close() db.close()
12. Security precautions
When doing database programming, be sure to pay attention to security. Avoid directly inserting user-supplied data into SQL statements to prevent SQL injection attacks. Security can be enhanced using parameterized queries or ORM (Object Relational Mapping).
Summary
MySQL database programming is a key component of Python applications. This article explains how to connect to a database, create data tables, insert, query, update and delete data, and perform transactions. Through these operations, you can effectively manage data and ensure data integrity and security. Hope this article is helpful for you in MySQL database programming in Python.
The rapid rise of Python is extremely beneficial to the entire industry, but “There are many popular people and not many people
“, which has led to a lot of criticism, but it still cannot stop its popularity. development momentum.
If you are interested in Python and want to learn Python, here I would like to share with you a Complete set of Python learning materials, which I compiled during my own study. I hope it can help you, let’s work together!
Friends in need can click the link below to get it for free or Scan the QR code below to get it for free
Click here
1Getting started with zero basics
① Learning route
For students who have never been exposed to Python, we have prepared a detailed Learning and Growth Roadmap for you. It can be said to be the most scientific and systematic learning route. You can follow the above knowledge points to find corresponding learning resources to ensure that you learn more comprehensively.
② Route corresponding learning video
There are also many learning videos suitable for beginners. With these videos, you can easily get started with Python~
③Exercise questions
After each video lesson, there are corresponding exercises to test your learning results haha!
2Domestic and foreign Python books and documents
① Documents and books
3Python toolkit + project source code collection
①Python toolkit
The commonly used development software for learning Python is here! Each one has a detailed installation tutorial to ensure you can install it successfully!
②Python practical case
Optical theory is useless. You must learn to type code along with it and practice it in order to apply what you have learned to practice. At this time, you can learn from some practical cases. 100+ practical case source codes are waiting for you!
③Python mini game source code
If you feel that the practical cases above are a bit boring, you can try writing your own mini-game in Python to add a little fun to your learning process!
4Python interview questions
After we learn Python, we can go out and find a job if we have the skills! The following interview questions are all from first-tier Internet companies such as Alibaba, Tencent, Byte, etc., and Alibaba bosses have given authoritative answers. I believe everyone can find a satisfactory job after reviewing this set of interview materials.
5Python part-time channel
Moreover, after learning Python, you can also take orders and make money on major part-time platforms. I have compiled various part-time channels + part-time precautions + how to communicate with customers into documents.
All the above information , if friends need it, you can scan the QR code below to get it for free