How to use Python scripts to operate MySQL database in Linux system

How to use Python scripts to operate MySQL database in Linux system

With the increasing demand for data processing and storage, MySQL database has become a common choice among developers Choose one. In Linux systems, it is very convenient to use Python scripts to interact with MySQL databases. This article will introduce how to use Python scripts to operate MySQL databases in Linux systems and provide some specific code examples.

First, we need to ensure that the MySQL database has been installed in the system and the Python development environment has been correctly configured. If the MySQL database has not been installed, you can use the following command to install it:

sudo apt-get update
sudo apt-get install mysql-server

Next, we need to install the MySQL driver for Python, which can be installed using the following command:

pip install mysql-connector-python

After the installation is complete , we can start using Python scripts to operate the MySQL database. Below is a simple example that demonstrates how to connect to a MySQL database, create a table, insert data, query data, and close the connection:

import mysql.connector

# 连接到MySQL数据库
cnx = mysql.connector.connect(user='用户名', password='密码',
                              host='主机', database='数据库名')

# 创建表格
cursor = cnx.cursor()
create_table_query = '''
    CREATE TABLE books (
        id INT PRIMARY KEY AUTO_INCREMENT,
        title VARCHAR(100),
        author VARCHAR(100),
        price FLOAT
    )
'''
cursor.execute(create_table_query)
cnx.commit()

# 插入数据
insert_data_query = '''
    INSERT INTO books (title, author, price)
    VALUES (%s, %s, %s)
'''
data = [
    ('Python编程入门', '张三', 29.99),
    ('数据结构与算法', '李四', 39.99),
    ('机器学习实战', '王五', 49.99)
]
cursor.executemany(insert_data_query, data)
cnx.commit()

# 查询数据
select_data_query = 'SELECT * FROM books'
cursor.execute(select_data_query)
result = cursor.fetchall()

for row in result:
    print(row)

# 关闭连接
cursor.close()
cnx.close()

In the above example, we first use mysql.connector.connect () function connects to the MySQL database and requires the corresponding user name, password, host and database name. Then, we use the cursor.execute() function to execute SQL statements, including creating tables, inserting data, and querying data. Finally, obtain the query results through the cursor.fetchall() function, and use the for loop to traverse and output each row of data. Finally, we close the connection through the cursor.close() and cnx.close() functions. Note that we need to use the cnx.commit() function to commit the changes after all operations have been performed.

In addition to the above basic operations, you can also use Python scripts to perform some advanced database operations, such as updating data, deleting data, and processing transactions. By understanding the SQL syntax of the MySQL database and the basic syntax of Python scripts, we can perform database operations more flexibly. In addition, you can use ORM (Object Relational Mapping) tools, such as SQLAlchemy, to simplify database operations.

To summarize, we can easily operate the MySQL database using Python scripts in Linux systems. This article explains how to connect to a MySQL database, create tables, insert data, query data, and close connections, and provides specific code examples. Through continuous learning and practice, we can further master database operation skills and improve development efficiency.

The above is the detailed content of How to use Python scripts to operate MySQL database in Linux system. For more information, please follow other related articles on the PHP Chinese website!