• Python3使用PyMySQL操作数据库


    1. 安装PyMySQL

    pip install PyMySQL

    关于PyMySQL的详细内容可以查看官方文档  Github

    2. 创建表

    在某个数据库内,使用以下指令建表

    CREATE TABLE `users` 
    (
      `id` int(11) NOT NULL AUTO_INCREMENT,
      `email` varchar(255)  NOT NULL,
      `password` varchar(255)  NOT NULL,
      PRIMARY KEY (`id`)
    ) ENGINE=InnoDB CHARSET=utf8 COLLATE=utf8_bin

    3. 使用PyMySQL执行增删改查

    #!/usr/bin/python3
    # -*- coding: utf-8 -*-
    # @Time    : 2019-1-7 15:43
    # @Author  : Z.C.Wang
    # @Email   : iwangzhengchao@gmail.com
    # @File    : PyConnectionMySQL.py
    # @Software: PyCharm Community Edition
    """
    Description :
    pymysql.Connect()参数说明
    host(str):      MySQL服务器地址
    port(int):      MySQL服务器端口号
    user(str):      用户名
    passwd(str):    密码
    db(str):        数据库名称
    charset(str):   连接编码
    
    connection对象支持的方法
    cursor()        使用该连接创建并返回游标
    commit()        提交当前事务
    rollback()      回滚当前事务
    close()         关闭连接
    
    cursor对象支持的方法
    execute(sql, args)     执行一个数据库的查询命令
    fetchone()      取得结果集的下一行
    fetchmany(size) 获取结果集的下几行
    fetchall()      获取结果集中的所有行
    close()         关闭游标对象
    """
    import pymysql
    
    # 连接数据库
    connection = pymysql.connect(host='localhost', user='root', password='root',
                                 db='test', charset='utf8')
    
    if connection.open:
        print('the connection is open...')
    
    # 清空users表
    cursor = connection.cursor()
    cursor.execute("truncate table users")
    connection.commit()
    
    # (1)批量插入
    record = []
    for i in range(10):
        email = "mail_" + str(i) + "@qq.com"
        password = "xxx_" + str(i)
        record.append((email, password))
    
    try:
        sql = "insert into users (email, password) values (%s, %s)"
        rows = cursor.executemany(sql, record)
        connection.commit()
        print("insert success. affected rows : %d" % rows)
    except:
        print("insert ERROR.")
        connection.rollback()
    
    # (2)删除记录
    try:
        sql = "delete from users where id=1"
        rows = cursor.execute(sql)
        connection.commit()
        print("delete success. affected rows : %d" % rows)
    except:
        print("delete ERROR.")
        connection.rollback()
    
    # (3)修改记录
    try:
        sql = "update users set password='yyy' where id=5"
        rows = cursor.execute(sql)
        connection.commit()
        print("update success. affected rows : %d" % rows)
    except:
        print("update ERROR.")
        connection.rollback()
    
    # (4)查询记录
    try:
        sql = 'select * from users'
        count = cursor.execute(sql)
        print("number of record in users: %d" % count)
        result = cursor.fetchall()
        for row in result:
            print(row)
        connection.commit()
    except:
        print("query ERROR.")
        connection.rollback()
    
    # 关闭连接
    cursor.close()
    connection.close()
    print("connection close.")

    运行结果:

    D:Python3.6_2python.exe E:/PycharmProjects/PyConnectionMySQL/PyConnectionMySQL.py
    the connection is open...
    insert success. affected rows : 10
    delete success. affected rows : 1
    update success. affected rows : 1
    number of record in users: 9
    (2, 'mail_1@qq.com', 'xxx_1')
    (3, 'mail_2@qq.com', 'xxx_2')
    (4, 'mail_3@qq.com', 'xxx_3')
    (5, 'mail_4@qq.com', 'yyy')
    (6, 'mail_5@qq.com', 'xxx_5')
    (7, 'mail_6@qq.com', 'xxx_6')
    (8, 'mail_7@qq.com', 'xxx_7')
    (9, 'mail_8@qq.com', 'xxx_8')
    (10, 'mail_9@qq.com', 'xxx_9')
    connection close.
    
    Process finished with exit code 0
  • 相关阅读:
    【高斯消元】BZOJ 1770: [Usaco2009 Nov]lights 燈
    【高斯消元】Poj 1222:EXTENDED LIGHTS OUT
    【高斯消元】BZOJ 1013: [JSOI2008]球形空间产生器sphere
    【数学】[BZOJ 3884] 上帝与集合的正确用法
    【数学/扩展欧几里得/线性求逆元】[Sdoi2008]沙拉公主的困惑
    【数学/扩展欧几里得/Lucas定理】BZOJ 1951 :[Sdoi 2010]古代猪文
    【扩展欧几里得】Bzoj 1407: [Noi2002]Savage
    [51nod2935] 土地划分
    [51nod2982] 大逃杀
    [BZOJ1005] HNOI2008 明明的烦恼
  • 原文地址:https://www.cnblogs.com/iwangzhengchao/p/10235596.html
Copyright © 2020-2023  润新知