python数据库编程

pymysql模块

pymysql是Python中操作MySQL的模块，其使用方法和MySQLdb几乎相同。但目前pymysql 支持python3.x而后者不支持3.x版本。

pymysql是第三方模块，需要单独安装，首选通过pip安装PyMySQL:

# pip3 install pymysql

基本使用

先创建一个test库，再创建一张user表： 
create database test; 
use test; 
create table user( id int primary key not null auto_increment, username varchar(50) not null, password varchar(50) not null );

import pymysql 
conn = pymysql.connect( 
    host='192.168.1.11', 
    port=3306, 
    user='root', 
    password='123456', 
    db='test', 
    charset='utf8', 
    cursorclass=pymysql.cursors.DictCursor 
)

cursor = conn.cursor() 
# 插入单条记录 
sql = "insert into user(username, password) values ('amei', '123456')" 
cursor.execute(sql) 
conn.commit() 
conn.close()

增删改查

示例：遍历查询结果

import pymysql 
conn = pymysql.connect(
    host='192.168.1.12', 
    port=3308, 
    user='root', 
    password='123456', 
    db='test', 
    charset='utf8', 
    cursorclass=pymysql.cursors.DictCursor
) 

cursor = conn.cursor()

with conn.cursor() as cursor: 
    sql = "select id,username,password from user" 
    cursor.execute(sql) 
    result = cursor.fetchall()
for dict in result: 
    print(f"ID: {dict['id']}, 用户名: {dict['username']}, 密码: {dict['password']}")