python3之pymysql

发布时间:2019-09-28 08:39:10编辑:auto阅读(1775)

    pymsqlPython中操作MySQL的模块并且只有在Python3.0版本中才存在,其使用方法和MySQLdb几乎相同

    下载安装pymsql模块

    pip3 install pymysql

    操作前准备

    #1.创建数据库
    mysql> create database mydb;
    mysql> use mydb;
    
    #2.创建表
    create table students
        (
            id int  not null auto_increment primary key,
            name char(8) not null,
            sex char(4) not null,
            age tinyint unsigned not null,
            tel char(13) null default "-"
        );
    
    #3.插入两条数据
    mysql> insert  into students values(1,"jack","M",20,"stu");
    mysql> insert  into students values(2,"xander","M",20,"stu");

    1.执行SQL

    import pymysql
    
    # 创建mysql连接(socket),client --> server
    """
    host = "Server端IP"
    port = "Server端口"
    user = "Server端用户"
    passwd = "Server端密码"
    db = "Server端数据库名"
    """
    conn = pymysql.connect(host="10.0.0.51",port=3306,user="root",passwd="123456",db="mydb")
    
    #创建游标(光标位置),相当于是socket上的实例
    cursor = conn.cursor()
    
    # 执行SQL语句,并返回受影响的行数
    """
    cursor.execute("需要执行的sql语句")
    """
    effect_row = cursor.execute("select * from students")
    print(cursor.fetchone())    # 获取第一条数据
    print(cursor.fetchone())    # 获取第二条数据
    print("------")
    print(cursor.fetchall())    # 获取所有数据(从未被获取的数据中读出来)
    
    # 提交SQL语句执行结果,不然无法保存新建或者修改的数据
    conn.commit()
    
    # 关闭游标
    cursor.close()
    
    # 关闭MySQL连接
    conn.close()

    2.插入数据

    import pymysql
    
    # 创建mysql连接(socket),client --> server
    conn = pymysql.connect(host="10.0.0.51",port=3306,user="root",passwd="123456",db="mydb")
    
    #创建游标(光标位置),相当于是socket上的实例
    cursor = conn.cursor()
    
    # 定义需要插入的数据
    data = [
        ("Daniel","M","21","stu"),
        ("Noah","M","25","stu"),
        ("David", "M", "25", "stu")
    ]
    
    # 执行插入SQL语句
    cursor.executemany("insert into students(name,sex,age,tel) values(%s,%s,%s,%s)" ,data)
    
    # 提交SQL语句执行结果,不然无法保存新建或者修改的数据
    conn.commit()
    
    # 关闭游标
    cursor.close()
    
    # 关闭MySQL连接
    conn.close()

关键字