Pymysql的使用

一、使用MySQL workbench建立数据库、表

创建spider数据库

?

1 2 3

drop database spider; create database spider; ALTER DATABASE spider CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;

创建users表 

?

1 2 3 4 5 6 7 8 9

use spider; drop table if exists users; create table users( id int(11) NOT NULL auto_increment, email varchar(255) collate utf8_bin NOT NULL, password varchar(255) collate utf8_bin NOT NULL, primary key(id) )engine=InnoDB default charset=utf8mb4 collate=utf8mb4_bin auto_increment=1;

　

二、安装pymysql

 安装pymysql

 三、使用pymysql

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26

import pymysql.cursors   #Connection to the database connection=pymysql.connect(     host='127.0.0.1',     port=3306,     user='root',     passwd='123456',     db='spider',     charset='utf8',     cursorclass=pymysql.cursors.DictCursor) try:     # 插入元素     with connection.cursor() as cursor:         sql="INSERT INTO users (email,password) VALUES (%s,%s)"         cursor.execute(sql,('webmaster@python.org','very-secret'))     connection.commit()           # 查询数据库的值     with connection.cursor() as cursor:         sql = "SELECT id,password FROM users WHERE email=%s"         cursor.execute(sql, ('webmaster@python.org'))         result = cursor.fetchone()         print(result) finally:     connection.close()

 运行结果：

 执行了三次，所以有三条数据