• 爬虫学习之-操作mysql


    在操作数据库的时候,python2中一般使用mysqldb,但在python3中已经不在支持mysqldb了,我们可以用pymysql和mysql.connector。本文的所有操作都是在python3的pymysql下完成的。

    一、基本操作

    (1) 查询

    import pymysql
    
    conn = pymysql.connect(host='127.0.0.1', user='root', passwd='root', db='test')
    cur = conn.cursor()
    
    # 查询
    sql = "select * from info"
    reCount = cur.execute(sql)  # 返回受影响的行数
    print(reCount)
    data = cur.fetchall()  # 返回数据,返回的是tuple类型
    print(data)
    """
    ((1, 'mj', 'tokyo'), (2, 'alex', 'newyork'), (3, 'tommy', 'beijing'))
    """
    
    cur.close()
    conn.close()

    (2) 修改

    import pymysql
    
    conn = pymysql.connect(host='127.0.0.1', user='root', passwd='root', db='test')
    cur = conn.cursor()
    
    # 插入数据
    sql2 = "insert into info(NAME,address ) VALUES(%s,%s)"  # sql语句,%s是占位符(%s是唯一的,不论什么数据类型都使用%s)用来防止sql注入
    params = ('eric', 'wuhan')  # 参数
    reCount = cur.execute(sql2, params)
    
    # 批量插入
    li = [('a1', 'b1'), ('a2', 'b2')]
    sql3 = 'insert into info(NAME ,address) VALUES (%s,%s)'
    reCount = cur.executemany(sql3, li)
    
    conn.commit()  # 提交,执行多条命令只需要commit一次就行了
    
    cur.close()
    conn.close()

    (3) 返回dict类型的数据

    import pymysql
    
    conn = pymysql.connect(host='127.0.0.1', user='root', passwd='root', db='test')
    # cur = conn.cursor()
    cur = conn.cursor(cursor=pymysql.cursors.DictCursor)    #创建cursor的时候,指定1其返回的cursor类型为dict
    
    # 查询
    sql = "select * from info"
    reCount = cur.execute(sql)  # 返回受影响的行数
    print(reCount)
    data = cur.fetchall()  # 返回数据,返回的是tuple类型
    print(data)
    
    cur.close()
    conn.close()
    
    """
    [{'address': 'tokyo', 'name': 'mj', 'id': 1}, {'address': 'newyork', 'name': 'alex', 'id': 2}, {'address': 'beijing', 'name': 'tommy', 'id': 3}]
    """

    (4)获取自增id

    通过cur.lastrowid来获取自增id

    
    # 插入数据
    sql = "insert into info(NAME,address ) VALUES(%s,%s)" 
    params = ('eric', '/usr/bin/a.txt')  
    reCount = cur.execute(sql, params)
    conn.commit()
    
    new_id = cur.lastrowid  #获取自增id,提交完之后才能取到值
    print(new_id)

    二、cursor定位

    使用fechone来逐条获取数据

    data = cur.fetchone()
    print(data)  
    
    data = cur.fetchone()
    print(data)  
    
    data = cur.fetchone()
    print(data)  
    
    """
    (1, 'mj', 'tokyo')
    (2, 'alex', 'newyork')
    (3, 'tommy', 'beijing')
    """

    (1) 绝对定位

    cur.scroll(0,mode='absolute')
    data = cur.fetchone()
    print(data) 
    
    cur.scroll(0,mode='absolute')
    
    data = cur.fetchone()
    print(data) 
    
    data = cur.fetchone()
    print(data)
    """
    (1, 'mj', 'tokyo')
    (1, 'mj', 'tokyo')
    (2, 'alex', 'newyork')
    """

    (2) 相对定位

    cur.scroll(-1,mode='relative')
    data = cur.fetchone()
    print(data) 
    
    data = cur.fetchone()
    print(data)  
    
    cur.scroll(-1,mode='relative')
    
    data = cur.fetchone()
    print(data)
    """
    (1, 'mj', 'tokyo')
    (2, 'alex', 'newyork')
    (2, 'alex', 'newyork')
    """

    三、解耦

    这里简单实现一个用户登录的功能,以便对整个业务结构有一个整体的认识。

    项目目录结构

    index.py

    #!/usr/bin/env python3
    # -*- coding:utf-8 -*-
    
    from model.admin import Admin
    
    
    def main():
        usr = input("username:")
        pwd = input("password:")
        admin = Admin()
        result = admin.CheckValidate(usr, pwd)
        if not result:  # 一般会把简单的逻辑放在上面,复杂的逻辑放下面
            print("登录失败!")
        else:
            print("登陆成功!进入后台管理界面..")
    
    
    if __name__ == "__main__":
        main()

    admin.py

    #!/usr/bin/env python3
    # -*- coding:utf-8 -*-
    from utility.sql_helper import MySqlHelper
    
    
    class Admin():
        def __init__(self):
            self.__helper = MySqlHelper()
    
        def CheckValidate(self,username,password):
            sql = "select * from admin where username=%s and password=%s"
            params=(username,password)
            return self.__helper.getOne(sql,params)

    sql_helper.py

    #!/usr/bin/env python3
    # -*- coding:utf-8 -*-
    import pymysql
    import conf
    
    
    class MySqlHelper(object):
        def __init__(self):
            self.__conn_dict = conf.conn_dict  # 把数据库连接信心提取到conf中
    
        def getDict(self, sql, params):
            conn = pymysql.connect(**self.__conn_dict)
            cur = conn.cursor(cursor=pymysql.cursors.DictCursor)
            cur.execute(sql, params)
            data = cur.fetchall()
            cur.close()
            conn.close()
            return data
    
        def getOne(self, sql, params):
            conn = pymysql.connect(**self.__conn_dict)  # 加**后表示传入的是字典里的数据,否则报错
            cur = conn.cursor(cursor=pymysql.cursors.DictCursor)
            cur.execute(sql, params)
            data = cur.fetchone()
            cur.close()
            conn.close()
            return data

    conf.py

    #!/usr/bin/env python3
    # -*- coding:utf-8 -*-
    
    conn_dict = dict(host='127.0.0.1', user='root', passwd='root', db='test')
     

  • 相关阅读:
    Elasticsearch排序与相关性
    Elasticsearch请求体查询
    编码问题
    Mybatis
    Spring Framework
    线上的一些坑记录
    【小程序】---- 实现授权与登录的基本流程
    【小程序】---- 封装请求
    【小程序】---- 自定义数字键盘拨号
    不为人知的网络编程(十二):彻底搞懂TCP协议层的KeepAlive保活机制
  • 原文地址:https://www.cnblogs.com/brady-wang/p/9683060.html
Copyright © 2020-2023  润新知