Pymysql的使用

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

创建spider数据库

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

创建users表 

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

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()

 运行结果：

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