Python connection to MySQL database programming

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

  1. Install MySQL driver
  2. Connect to MySQL database
  3. Create database
  4. Create data table
  5. Insert data
  6. Query data
  7. update data
  8. delete data
  9. Execute transaction
  10. Close database connection
  11. Example: A complete MySQL database programming example
  12. Security considerations
  13. 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