Python 下 pymsql 库的基本使用

发表于 更新于 分类于 阅读次数： Disqus： 本文字数： 97 阅读时长 ≈ 1 分钟

库的导入

import pymysql

连接 MySql

  1. 连接配置 
    1
    2
    3
    4
    MYSQL_HOST = 'localhost'
    MYSQL_DBNAME = 'cnkiQuote'
    MYSQL_USER = 'root'
    MYSQL_PASSWD = '123456'
    从上往下依次是主机 ip，数据库名，用户名及密码。
  2. 连接 指定编码为 utf8 
    1
    2
    3
    4
    5
    6
    7
    connection=pymysql.connect(
        host=MYSQL_HOST,
        db=MYSQL_DBNAME,
        user=MYSQL_USER,
        passwd=MYSQL_PASSWD,
        charset='utf8',
        use_unicode=True)

Sql 操作

  1. 查询

    1
    2
    3
    4
    connection.cursor.execute("""select * from periodical_data where title = %s and publicationDate = %s and author = %s""",
    (item["title"],
    item['publicationDate'],
    str(item['author'])))

  2. 插入

    1
    2
    3
    4
    5
    6
    7
    connection.cursor.execute("""insert into periodical_data(title,author,journalName,publicationDate,referenceNum, downloadNum) value (%s,%s,%s,%s,%s,%s)""",
    (item['title'],
    str(item['author']),
    item['journalName'],
    item['publicationDate'],
    item['referenceNum'],
    item['downloadNum']))

  3. 更新

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    connection.cursor.execute("""update periodical_data set title = %s,author = %s,journalName = %s, publicationDate = %s,referenceNum = %s,downloadNum = %s where title = %s and publicationDate = %s and author = %s""",
    (str(item['title']),
    str(item['author']),
    str(item['journalName']),
    item['publicationDate'],
    item['referenceNum'],
    item['downloadNum'],
    str(item['title']),
    item['publicationDate'],
    str(item['author'])))

  4. 提交更改
    connection.commit()