Python 操作mysql数据库

---

 

 首先安装mysql-connector模块：

pip install mysql-connector

 

---

 

模块使用方式：

# 引入模块
import mysql.connector;

# 连接数据库
db = mysql.connector.connect(
    host = "localhost",
    user = "root",
    passwd = "123456",
    database = "test"
);

# 创建游标
cursor = db.cursor();

# 创建表
sql = '''
    CREATE TABLE `test`
    (
        `id` int(11) NOT NULL AUTO_INCREMENT,
        PRIMARY KEY (`id`)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
''';
cursor.execute(sql);

# 增加字段与索引
cursor.execute("ALTER TABLE TEST ADD COLUMN `name` VARCHAR(24) NOT NULL DEFAULT '';");
cursor.execute("ALTER TABLE TEST ADD COLUMN `gender` ENUM('男', '女') NOT NULL DEFAULT '男';");
cursor.execute("ALTER TABLE TEST ADD INDEX `gender`(`gender`);");

# 插入数据并获取自增ID
cursor.execute("INSERT INTO `test`(`name`, `gender`) VALUES ('何效名', '男');");
db.commit(); # 插入、更新、删除语句必须提交才能生效
print("记录已插入, ID:", cursor.lastrowid);

# 批量插入数据
sql = "INSERT INTO `test`(`name`, `gender`) VALUES (%s, %s);";
val = [
    ('刘看山', '男'),
    ('钉三多', '男'),
    ('小爱同学', '女')
];
cursor.executemany(sql, val);
db.commit(); # 插入、更新、删除语句必须提交才能生效

# 查询数据
cursor.execute("SELECT * FROM `test` WHERE `gender` = '女';");
result = cursor.fetchall();
print(result);