Python server programming: Interacting with MySQL databases using PyMySQL

Python is a popular high-level programming language with a rich library and framework that can be used to develop various types of applications. In the field of server programming, Python can be used not only to develop web applications but also to interact with various databases.

This article will introduce how to use Python to interact with the MySQL database. We will use PyMySQL as the library for Python to interact with MySQL.

1. Install the PyMySQL library

Before using the PyMySQL library, you need to install it first. Open a terminal or command prompt and enter the following command to install:

pip install PyMySQL

2. Connect to the MySQL database

To connect to the MySQL database, we need to know the host name and port number of the MySQL server ,user name and password.

We can connect to the MySQL database using the following Python code:

import pymysql

# 打开数据库连接
db = pymysql.connect(host='localhost', port=3306, user='root', password='', database='test')

# 关闭数据库连接
db.close()

In this code, we have used the connect() of the pymysql library Function to connect to a MySQL database. The parameters of this function include information such as host name, port number, user name and password.

In addition, we also specify the name of the database to connect to, which is passed through the database parameter. In the code below, we will continue to use this connection.

3. Create a table

Before interacting with the MySQL database, we need to create a table. We can create a table named users in a MySQL database using the following Python code:

import pymysql

# 打开数据库连接
db = pymysql.connect(host='localhost', port=3306, user='root', password='', database='test')

# 创建表
cursor = db.cursor()
cursor.execute('DROP TABLE IF EXISTS users')
sql = '''CREATE TABLE users (
         id INT(11) NOT NULL AUTO_INCREMENT,
         name VARCHAR(20) NOT NULL,
         email VARCHAR(30) NOT NULL,
         password VARCHAR(30) NOT NULL,
         PRIMARY KEY (id))'''
cursor.execute(sql)

# 关闭数据库连接
db.close()

In this code, we define a table named users table and defines four columns: id, name, email, and password. Among them, id is the primary key, using the INT data type, name and email are saved using the VARCHAR data type The string, password is also a string saved using the VARCHAR data type. The table creation process uses MySQL SQL statements. Note that we also set up an auto-increment column in the users table, which will automatically increment.

4. Insert data

Now that we have created the users table, we can start inserting data into the table. We can insert some data into this table using the following Python code:

import pymysql

# 打开数据库连接
db = pymysql.connect(host='localhost', port=3306, user='root', password='', database='test')

# 插入数据
cursor = db.cursor()
sql = "INSERT INTO users(name, email, password) VALUES (%s, %s, %s)"
values = [('Alice', 'alice@email.com', '123456'),
          ('Bob', 'bob@email.com', '789012'),
          ('Charlie', 'charlie@email.com', '345678')]
cursor.executemany(sql, values)
db.commit()

# 关闭数据库连接
db.close()

In this code, we first insert multiple data into users using the executemany() function table. The first parameter of this function specifies the SQL statement to be executed, and the second parameter is a tuple or list containing the data to be inserted. Note that we used the placeholder %s to represent the actual value to be inserted.

After calling the executemany() function, we must call the commit() method to save the data to the database.

5. Query data

To retrieve data from the users table, we can use the following Python code:

import pymysql

# 打开数据库连接
db = pymysql.connect(host='localhost', port=3306, user='root', password='', database='test')

# 查询数据
cursor = db.cursor()
sql = "SELECT * FROM users"
cursor.execute(sql)
results = cursor.fetchall()
for row in results:
    id = row[0]
    name = row[1]
    email = row[2]
    password = row[3]
    print("{} - {} - {} - {}".format(id, name, email, password))

# 关闭数据库连接
db.close()

In this code, we Use the SELECT statement to retrieve all rows from the users table. Then, use the fetchall() method to get all retrieved rows and use a loop to process each row as a tuple. Finally, we print the output based on the column values ​​of each row.

6.Update data

To update the data in the users table, we can use the following Python code:

import pymysql

# 打开数据库连接
db = pymysql.connect(host='localhost', port=3306, user='root', password='', database='test')

# 更新数据
cursor = db.cursor()
sql = "UPDATE users SET password = %s WHERE name = %s"
values = ('abcdef', 'Alice')
cursor.execute(sql, values)
db.commit()

# 关闭数据库连接
db.close()

In this code, we Use the UPDATE statement to update the password of the user named Alice to abcdef. The code uses a SQL statement with parameters, where %s is a placeholder. We also wrap the actual value to be updated in a tuple and pass it to the execute() function.

7. Delete data

To delete data from users table, we can use the following Python code:

import pymysql

# 打开数据库连接
db = pymysql.connect(host='localhost', port=3306, user='root', password='', database='test')

# 删除数据
cursor = db.cursor()
sql = "DELETE FROM users WHERE email = %s"
value = ('bob@email.com',)
cursor.execute(sql, value)
db.commit()

# 关闭数据库连接
db.close()

In this code, we Use the DELETE statement to delete the user whose email address is bob@email.com.

Summary

In this article, we introduced how to use Python to interact with the MySQL database and used the pymysql library as a bridge between Python and MySQL. We learned how to connect to a MySQL database, create tables, insert data, update data, and delete data, which are common tasks that are often involved when developing web applications. By mastering these skills, you can start using Python to build powerful server applications, as well as interact with a variety of databases.

The above is the detailed content of Python server programming: Interacting with MySQL databases using PyMySQL. For more information, please follow other related articles on the PHP Chinese website!