The key to connecting to a MySQL database in Python is to select the right library and configure it correctly. 1. Install the driver: Use pip to install mysql-connector-python or pymysql; 2. Create a connection: Create a connection by specifying host, user, password and database parameters; 3. Execute a query: Create a cursor object, call the execute() method to execute SQL statements, and use fetchall() to get the results; 4. Submit a transaction or close the connection: For the modification operation, the transaction needs to be submitted, and finally the cursor and database connection are closed; 5. When using PyMySQL, the import module and connection methods are slightly different, and more customization options are supported. Common problems are mostly caused by network or permission configuration and need to be checked one by one.

Connecting to MySQL database is actually not difficult in Python, mainly using tools and writing and configuration. The most commonly used method is to implement it through the two libraries mysql-connector-python or PyMySQL . Below I will explain how to operate it in several steps, including installation, connection and simple query.

Install MySQL driver

Python itself does not have built-in MySQL support, so the first step is to install a driver package first. There are two more common options:

• mysql-connector-python (official recommendation)
• pymysql (lighter, community-maintained)

You can use pip to install one of them, for example:

 pip install mysql-connector-python
# or pip install pymysql

If you are not sure which one to choose, you can give mysql-connector-python priority, it has complete functions and complete documentation.

Establish a database connection

After installing the driver, you can start writing code to connect to the database. Taking mysql-connector-python as an example, the basic connection method is as follows:

 import mysql.connector

conn = mysql.connector.connect(
    host='localhost',
    user='your_username',
    password='your_password',
    database='your_database'
)

Note that these four parameters are necessary: host address, user name, password, and database name. If the database is not local, you need to change host to the IP address or domain name of the remote server.

Sometimes you can't connect, which may be a firewall or permissions problem. Remember to confirm whether the database allows remote access and whether the user has corresponding permissions.

Execute queries and get results

After the connection is successful, the next step is to execute the SQL query. You need to create a cursor object and then call its execute() method:

 cursor = conn.cursor()
cursor.execute("SELECT * FROM users LIMIT 5")

for row in cursor.fetchall():
    print(row)

This way, the first five records can be printed out. Note that after each SQL is executed, if it is a query statement, use fetchall() or fetchone() to get the data. If it is an insertion, update and other operations, you need to call conn.commit() to submit the transaction.

Don't forget to turn off the cursor and connection at the end:

 cursor.close()
conn.close()

The difference between using PyMySQL

If you are using PyMySQL , the code structure is similar, except that the import module and connection methods are slightly different:

 import pymysql

conn = pymysql.connect(
    host='localhost',
    user='your_username',
    password='your_password',
    database='your_database',
    charset='utf8mb4',
    cursorclass=pymysql.cursors.DictCursor # optional, return the result in dictionary format)

It supports more customization options such as setting character sets and returning formats. Overall, the logic of the use of the two is consistent, but there are some differences in details.

Basically that's it. As long as the driver is installed and the parameters are installed, it will not be difficult to connect to the database. When encountering problems in the middle, most of them are because the network or permission configuration is incorrect, so you can check it step by step.

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