python数据库mysql开发

### Python MySQL 数据库开发教程

#### 使用Python操作MySQL数据库概述
对于希望掌握数据库操作技能的开发者来说，学习如何利用Python对MySQL执行基本的操作是一项重要能力[^1]。

#### 环境配置
确保安装了必要的软件包并设置了正确的环境变量。这通常涉及设置好Python解释器以及`pymysql`或其他兼容驱动程序的安装[^2]。

#### 创建连接实例
建立到MySQL服务器的安全连接是任何数据库交互的第一步：

import pymysql

connection = pymysql.connect(
    host='localhost',
    port=3306,
    user='root',
    password='your_password_here',
    database='test_db'
)

cursor = connection.cursor()
print('Database connected successfully.')

#### 执行SQL语句
一旦建立了稳定可靠的连接，则可以通过编写SQL命令来进行各种类型的CRUD (Create, Read, Update, Delete) 操作。

##### 插入新记录
向名为`students`的数据表内添加新的学生信息条目:

INSERT INTO students(name, age) VALUES ('Alice', 20);

对应的Python代码如下所示：

insert_sql = "INSERT INTO students(name, age) VALUES (%s, %d)"
data_tuple = ("Bob", 21)
try:
    cursor.execute(insert_sql, data_tuple)
    connection.commit()
except Exception as e:
    print(f"An error occurred while inserting record: {e}")
finally:
    pass

##### 查询现有数据
检索特定条件下满足要求的学生列表：

SELECT * FROM students WHERE age >= 18;

在Python中实现上述功能的方式为：

query_sql = "SELECT * FROM students WHERE age >= %d"
age_limit = 18
try:
    cursor.execute(query_sql % age_limit)
    results = cursor.fetchall()
    for row in results:
        print(row)
except Exception as e:
    print(f"Error fetching records: {e}")
finally:
    pass

##### 更新已有记录
修改指定学生的年龄属性值：

UPDATE students SET age=%d WHERE name='%s';

以下是更新操作的具体实施方法：

update_sql = "UPDATE students SET age=%d WHERE name='%s'"
new_age = 22
student_name = 'Charlie'

try:
    affected_rows = cursor.execute(update_sql % (new_age, student_name))
    if affected_rows > 0:
        connection.commit()
        print("Record updated.")
    else:
        print("No matching record found.")
except Exception as e:
    print(f"Failed to update record due to an exception: {e}")
finally:
    pass

##### 删除不再需要的数据
移除不符合当前业务逻辑需求的数据项：

DELETE FROM students WHERE id=%d;

下面展示了怎样安全有效地删除单个或多个实体对象：

delete_sql = "DELETE FROM students WHERE id=%d"

target_id = 5

try:
    deleted_count = cursor.execute(delete_sql % target_id)
    if deleted_count > 0:
        connection.commit()
        print("Deletion successful.")
    else:
        print("Target ID not exist.")
except Exception as e:
    print(f"Encountered problem during deletion process: {e}")
finally:
    pass

最后，在所有的读取/写入活动完成后应当关闭游标的会话通道，并断开与远程主机之间的物理链路以释放资源占用：

cursor.close()
connection.close()