Detailed steps to connect to MySQL in Python 3.6

Environmental preparation

Install Python 3.6

Make sure your system has Python 3.6 installed. You can check the currently installed Python version by entering python --version via the command line.

Install MySQL

If you have not installed MySQL yet, you can download the installation package suitable for your operating system from the MySQL official website and follow the instructions to complete the installation.

Install pymysql library

pymysql is a pure Python implementation MySQL client library that allows us to operate MySQL databases in Python. The installation method is as follows:

pip install pymysql

Connect to MySQL

Establish a connection

First, you need to importpymysqlmodule and useconnect()Method establishes a connection to the MySQL server. Here, take the local database as an example to show how to set connection parameters:

import pymysql
 
# Create a connectionconn = (
    host='127.0.0.1',  # Database IP address    user='root',       # Database username    password='password',  # Database Password    database='testdb',  # The database name used    charset='utf8mb4'   # Character encoding)
 
# Create a cursor objectcursor = ()

Execute SQL query

Once a connection is established, SQL statements can be executed through the cursor object. For example, create a table, insert data, query data, etc.:

# Create a tablecreate_table_sql = """
CREATE TABLE IF NOT EXISTS users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(255),
    email VARCHAR(255)
);
"""
(create_table_sql)
 
# Insert datainsert_data_sql = "INSERT INTO users (name, email) VALUES (%s, %s)"
data = ('Zhang San', 'zhangsan@')
(insert_data_sql, data)
()  # Submit transaction 
# Query dataquery_sql = "SELECT * FROM users"
(query_sql)
results = ()
for row in results:
    print(row)

Close the connection

After you have done everything, remember to close the cursor and connection to free up the resource:

()
()

Through these basic steps, you can further explore more complex application scenarios, such as transaction management, error handling, etc. The above is a detailed introduction on how to use the pymysql library to connect to MySQL database in Python 3.6. Connecting to MySQL databases in Python is usually done using pymysql or mysql-connector-python libraries. Below I will provide example code for these two libraries separately.

Connect to MySQL using pymysql

First, make sure you have installed the pymysql library. You can install it using the following command:

pip install pymysql

Then you can use the following code to connect to the MySQL database and perform some basic operations:

import pymysql
 
# Database connection configurationconfig = {
    'host': '127.0.0.1',
    'port': 3306,
    'user': 'your_username',
    'password': 'your_password',
    'database': 'your_database',
    'charset': 'utf8mb4'
}
 
try:
    # Create a connection    connection = (**config)
    
    # Create a cursor    cursor = ()
    
    # Execute SQL query    sql_query = "SELECT * FROM your_table"
    (sql_query)
    
    # Get query results    results = ()
    for row in results:
        print(row)
    
    # Close cursor and connection    ()
    ()
 
except  as e:
    print(f"Error: {e}")

Connect MySQL using mysql-connector-python

First, make sure you have installedmysql-connector-python library. You can install it using the following command:

pip install mysql-connector-python

Then you can use the following code to connect to the MySQL database and perform some basic operations:

import 
 
# Database connection configurationconfig = {
    'host': '127.0.0.1',
    'port': 3306,
    'user': 'your_username',
    'password': 'your_password',
    'database': 'your_database',
    'charset': 'utf8mb4'
}
 
try:
    # Create a connection    connection = (**config)
    
    # Create a cursor    cursor = ()
    
    # Execute SQL query    sql_query = "SELECT * FROM your_table"
    (sql_query)
    
    # Get query results    results = ()
    for row in results:
        print(row)
    
    # Close cursor and connection    ()
    ()
 
except  as e:
    print(f"Error: {e}")

Practical application scenarios

Suppose you have an e-commerce website that needs to obtain user order information from the database. You can use any of the above code to connect to the database and execute the query.

For example, you have a table named orders in your database that contains the following fields: order_id, user_id, product_id, quantity, order_date. You can use the following code to get order information for a specific user:

import pymysql
 
# Database connection configurationconfig = {
    'host': '127.0.0.1',
    'port': 3306,
    'user': 'your_username',
    'password': 'your_password',
    'database': 'your_database',
    'charset': 'utf8mb4'
}
 
try:
    # Create a connection    connection = (**config)
    
    # Create a cursor    cursor = ()
    
    # User ID    user_id = 123
    
    # Execute SQL query    sql_query = f"SELECT * FROM orders WHERE user_id = {user_id}"
    (sql_query)
    
    # Get query results    results = ()
    for row in results:
        print(row)
    
    # Close cursor and connection    ()
    ()
 
except  as e:
    print(f"Error: {e}")

Hope these examples help you! If you have any other questions, feel free to ask. Connecting to MySQL databases in Python 3.6 is usually usedmysql-connector-pythonlibrary orpymysqlLibrary. Below I will introduce how to use these two libraries to connect to MySQL database and perform some basic operations.

Install the necessary libraries

First, you need to install the corresponding library. You can install it through the pip command:

mysql-connector-python:

pip install mysql-connector-python

pymysql:

pip install pymysql

Connect MySQL using mysql-connector-python

Sample code

import 
from  import Error
 
def create_connection(host_name, user_name, user_password, db_name):
    connection = None
    try:
        connection = (
            host=host_name,
            user=user_name,
            passwd=user_password,
            database=db_name
        )
        print("Connection to MySQL DB successful")
    except Error as e:
        print(f"The error '{e}' occurred")
 
    return connection
 
def execute_query(connection, query):
    cursor = ()
    try:
        (query)
        ()
        print("Query executed successfully")
    except Error as e:
        print(f"The error '{e}' occurred")
 
def execute_read_query(connection, query):
    cursor = ()
    result = None
    try:
        (query)
        result = ()
        return result
    except Error as e:
        print(f"The error '{e}' occurred")
 
# Main Programif __name__ == "__main__":
    # Database connection information    host = "localhost"
    user = "root"
    password = "yourpassword"
    database = "testdb"
 
    # Create a connection    conn = create_connection(host, user, password, database)
 
    # Create a table    create_table_query = """
    CREATE TABLE IF NOT EXISTS users (
      id INT AUTO_INCREMENT PRIMARY KEY,
      name VARCHAR(255) NOT NULL,
      age INT
    ) ENGINE=InnoDB;
    """
    execute_query(conn, create_table_query)
 
    # Insert data    insert_data_query = """
    INSERT INTO users (name, age) VALUES ('Alice', 30), ('Bob', 25);
    """
    execute_query(conn, insert_data_query)
 
    # Query data    select_data_query = "SELECT * FROM users;"
    users = execute_read_query(conn, select_data_query)
    for user in users:
        print(user)
 
    # Close the connection    if conn.is_connected():
        ()
        print("MySQL connection is closed")

Connect to MySQL using pymysql

Sample code

import 
 
def create_connection(host, user, password, database):
    connection = None
    try:
        connection = (
            host=host,
            user=user,
            password=password,
            database=database,
            charset='utf8mb4',
            cursorclass=
        )
        print("Connection to MySQL DB successful")
    except  as e:
        print(f"The error '{e}' occurred")
 
    return connection
 
def execute_query(connection, query):
    with () as cursor:
        try:
            (query)
            ()
            print("Query executed successfully")
        except  as e:
            print(f"The error '{e}' occurred")
 
def execute_read_query(connection, query):
    with () as cursor:
        result = None
        try:
            (query)
            result = ()
            return result
        except  as e:
            print(f"The error '{e}' occurred")
 
# Main Programif __name__ == "__main__":
    # Database connection information    host = "localhost"
    user = "root"
    password = "yourpassword"
    database = "testdb"
 
    # Create a connection    conn = create_connection(host, user, password, database)
 
    # Create a table    create_table_query = """
    CREATE TABLE IF NOT EXISTS users (
      id INT AUTO_INCREMENT PRIMARY KEY,
      name VARCHAR(255) NOT NULL,
      age INT
    ) ENGINE=InnoDB;
    """
    execute_query(conn, create_table_query)
 
    # Insert data    insert_data_query = """
    INSERT INTO users (name, age) VALUES ('Alice', 30), ('Bob', 25);
    """
    execute_query(conn, insert_data_query)
 
    # Query data    select_data_query = "SELECT * FROM users;"
    users = execute_read_query(conn, select_data_query)
    for user in users:
        print(user)
 
    # Close the connection    ()
    print("MySQL connection is closed")

Summarize

The above example shows how to usemysql-connector-pythonandpymysqlThe library connects to the MySQL database and performs basic operations such as creating tables, inserting data, and querying data. You can choose the right library to use according to your needs. Hope these examples help you!

The above is the detailed steps for connecting to MySQL in Python 3.6. For more information about connecting to MySQL in Python 3.6, please follow my other related articles!