pymsql简单的使用

不废话直接上代码：

import pymysql

class MysqlConnection:

    '''

    单例模式获取数据库链接实例

    '''

    _instance = None

    def __new__(cls,  *args, **kwargs):

        if not cls._instance:

            cls._instance = pymysql.connect( *args, **kwargs)

        return cls._instance

if __name__ == '__main__':

    conn = MysqlConnection(db='spider_data',user='root',password='root',host='127.0.0.1')

    # 获取游标

    cursor = conn.cursor()

    sql = 'select {} from {} limit {}'.format(

        ','.join(('img_url','link','title')),

        'chinaz',

        '0,4'

    )

    # 执行sql语句,返回受影响的行数

    print(cursor.execute(sql))

    # 读取一条数据

    print(cursor.fetchone())

    # 读取多条，结果是一个tupe类型

    print(cursor.fetchmany(2))

    # 游标置零

    cursor.rownumber = 0

    # 读取全部

    print(cursor.fetchall())

    # 插入数据

    sql = 'insert into {}({}) values({})'.format('chinaz', ','.join(('img_url', 'link', 'title')),'\"img1\",\"link1\",\"title1\"')

    try:

        cursor.execute(sql)

        # 提交保存到数据库中

        conn.commit()

    except:

        # 回滚

        conn.rollback()

    # 关闭游标

    cursor.close()

    # 关闭和数据库的连接

    conn.close()

# 执行后的打印结果

"""

4

('http://topimg.chinaz.com/WebSiteimages/pkueducn/56abaa19-b68f-4338-b710-561168d3686e_2016_s.png', 'http://top.chinaz.com/Html/site_pku.edu.cn.html', '北京大学')

(('http://topimg.chinaz.com/WebSiteimages/fudaneducn/d95f4aa6-8fdc-4b3d-b2d7-94f4be58de77_2016_s.png', 'http://top.chinaz.com/Html/site_fudan.edu.cn.html', '复旦大学'), ('http://topimg.chinaz.com/WebSiteimages/wwwscueducn/79382c04-0541-4779-b05a-936a518a6cc8_2015_s.png', 'http://top.chinaz.com/Html/site_scu.edu.cn.html', '四川大学'))

(('http://topimg.chinaz.com/WebSiteimages/pkueducn/56abaa19-b68f-4338-b710-561168d3686e_2016_s.png', 'http://top.chinaz.com/Html/site_pku.edu.cn.html', '北京大学'), ('http://topimg.chinaz.com/WebSiteimages/fudaneducn/d95f4aa6-8fdc-4b3d-b2d7-94f4be58de77_2016_s.png', 'http://top.chinaz.com/Html/site_fudan.edu.cn.html', '复旦大学'), ('http://topimg.chinaz.com/WebSiteimages/wwwscueducn/79382c04-0541-4779-b05a-936a518a6cc8_2015_s.png', 'http://top.chinaz.com/Html/site_scu.edu.cn.html', '四川大学'), ('http://topimg.chinaz.com/WebSiteimages/wwwtsinghuaeducn/49499b0d-fc15-4be3-8ffe-a52d26469321_2017_s.png', 'http://top.chinaz.com/site_www.tsinghua.edu.cn.html', '清华大学'))

"""