Python实战MySQL之数据库操作全流程详解

import mysql.connector
from mysql.connector import Error
 
def connect_to_database():
    try:
        # 连接到MySQL数据库
        connection = mysql.connector.connect(
            host='localhost',
            user='yourusername',
            password='yourpassword',
            database='yourdatabase'
        )
        print("连接成功")
        return connection
    except Error as e:
        print("连接失败: ", e)
 
def create_database(connection):
    try:
        cursor = connection.cursor()
        # 使用execute方法执行SQL创建数据库
        cursor.execute("CREATE DATABASE IF NOT EXISTS mydatabase")
        print("数据库创建成功")
    except Error as e:
        print("数据库创建失败: ", e)
 
def create_table(connection):
    try:
        cursor = connection.cursor()
        # 使用execute方法执行SQL创建表
        cursor.execute("""
        CREATE TABLE IF NOT EXISTS userdata (
            id INT AUTO_INCREMENT PRIMARY KEY,
            username VARCHAR(255) NOT NULL,
            password VARCHAR(255) NOT NULL
        )
        """)
        print("表创建成功")
    except Error as e:
        print("表创建失败: ", e)
 
def insert_data(connection):
    try:
        cursor = connection.cursor()
        # 使用execute方法执行SQL插入数据
        cursor.execute("INSERT INTO userdata(username, password) VALUES (%s, %s)", ("admin", "admin"))
        connection.commit()
        print("数据插入成功")
    except Error as e:
        print("数据插入失败: ", e)
 
def read_data(connection):
    try:
        cursor = connection.cursor()
        # 使用execute方法执行SQL查询数据
        cursor.execute("SELECT * FROM userdata")
        rows = cursor.fetchall()
        for row in rows:
            print(row)
    except Error as e:
        print("数据查询失败: ", e)
 
def main():
    try:
        # 连接到数据库
        connection = connect_to_database()
        # 创建数据库
        # create_database(connection)
        # 创建表
        # create_table(connection)
        # 插入数据
        # insert_data(connection)
        # 读取数据
        read_data(connection)
    except Error as e:
        print("程序出现异常: ", e)
    finally:
        if connection.is_connected():
            connection.close()
            print("连接已关闭")
 
if __name__ == '__main__':
    main()

这段代码展示了如何使用Python连接MySQL数据库，创建数据库、创建表、插入数据以及查询数据。在实际应用中，需要根据具体情况替换掉yourusername, yourpassword, yourdatabase等占位符，并确保MySQL服务正在运行。