最新消息:20210816 当前crifan.com域名已被污染,为防止失联,请关注(页面右下角的)公众号

【已解决】Python中如何操作mysql

MySQL crifan 2708浏览 0评论

折腾:

【记录】Sequel中新建mysql数据库并新建表和相应字段

期间,在写pyspider的resultworker之前,先要去搞清楚:

python中如何操作mysql

也参考看了些帖子:

Pyspider把抓取的结果存入mysql数据库 | 拈花古佛

Pyspider实例之抓取数据并保存到MySQL数据库 – CSDN博客

pyspider的mysql数据存储接口 – CSDN博客

漫谈 Pyspider 网络爬虫的实践 – 工具资源 – 掘金

实现Pyspider爬虫结果的自定义ResultWorker – 简书

【MySQL.Connector】Python下读取数据库信息 – 程序园

但是写法不一,还是去找最通用的,最好用的库和写法

【已解决】Python3中选择合适的mysql的连接器或驱动

先去安装PyMySQL:

<code>➜  AutocarData which python
/Users/crifan/.local/share/virtualenvs/AutocarData-xI-iqIq4/bin/python
➜  AutocarData pipenv install pymysql
Installing pymysql…
Looking in indexes: https://pypi.python.org/simple
Collecting pymysql
  Using cached https://files.pythonhosted.org/packages/e5/07/c0f249aa0b7b0517b5843eeab689b9ccc6a6bb0536fc9d95e65901e6f2ac/PyMySQL-0.8.0-py2.py3-none-any.whl
Installing collected packages: pymysql
Successfully installed pymysql-0.8.0

Adding pymysql to Pipfile's [packages]…
Pipfile.lock (625834) out of date, updating to (b95d9c)…
Locking [dev-packages] dependencies…
Locking [packages] dependencies…
Updated Pipfile.lock (b95d9c)!
Installing dependencies from Pipfile.lock (b95d9c)…
  🐍   ▉▉▉▉▉▉▉▉▉▉▉▉▉▉▉▉▉▉▉▉▉▉▉▉▉▉▉▉▉▉▉▉ 1/1 — 00:00:00
➜  AutocarData pipenv graph
PyMySQL==0.8.0
pyspider==0.3.10
  - chardet [required: &gt;=2.2, installed: 3.0.4]
  - click [required: &gt;=3.3, installed: 6.7]
  - cssselect [required: &gt;=0.9, installed: 1.0.3]
  - Flask [required: &gt;=0.10, installed: 0.12.2]
    - click [required: &gt;=2.0, installed: 6.7]
    - itsdangerous [required: &gt;=0.21, installed: 0.24]
    - Jinja2 [required: &gt;=2.4, installed: 2.10]
      - MarkupSafe [required: &gt;=0.23, installed: 1.0]
    - Werkzeug [required: &gt;=0.7, installed: 0.14.1]
  - Flask-Login [required: &gt;=0.2.11, installed: 0.4.1]
    - Flask [required: Any, installed: 0.12.2]
      - click [required: &gt;=2.0, installed: 6.7]
      - itsdangerous [required: &gt;=0.21, installed: 0.24]
      - Jinja2 [required: &gt;=2.4, installed: 2.10]
        - MarkupSafe [required: &gt;=0.23, installed: 1.0]
      - Werkzeug [required: &gt;=0.7, installed: 0.14.1]
  - Jinja2 [required: &gt;=2.7, installed: 2.10]
    - MarkupSafe [required: &gt;=0.23, installed: 1.0]
  - lxml [required: Any, installed: 4.2.1]
  - pycurl [required: Any, installed: 7.43.0.1]
  - pyquery [required: Any, installed: 1.4.0]
    - cssselect [required: &gt;0.7.9, installed: 1.0.3]
    - lxml [required: &gt;=2.1, installed: 4.2.1]
  - requests [required: &gt;=2.2, installed: 2.18.4]
    - certifi [required: &gt;=2017.4.17, installed: 2018.4.16]
    - chardet [required: &gt;=3.0.2,&lt;3.1.0, installed: 3.0.4]
    - idna [required: &lt;2.7,&gt;=2.5, installed: 2.6]
    - urllib3 [required: &gt;=1.21.1,&lt;1.23, installed: 1.22]
  - six [required: &gt;=1.5.0, installed: 1.11.0]
  - tblib [required: &gt;=1.3.0, installed: 1.3.2]
  - tornado [required: &gt;=3.2,&lt;=4.5.3, installed: 4.5.3]
  - u-msgpack-python [required: &gt;=1.6, installed: 2.5.0]
  - wsgidav [required: &gt;=2.0.0, installed: 2.3.0]
    - defusedxml [required: Any, installed: 0.5.0]
</code>

然后去写代码测试效果:

期间,关于:

http://pymysql.readthedocs.io/en/latest/modules/connections.html

中的charset的是utf-8 还是utf8,需要去搞清楚:

【已解决】mysql中支持的字符编码字符集以及utf8的写法

然后继续写代码,想要给connect加上catch error,但是不知道是哪些error:

【已解决】pymysql中connect异常时有哪些错误类型

然后继续写代码。

然后继续去试试删除table,新建table(如果不存在的话)

而对于创建table的sql语法如何写,可以参考Sequel中的:

<code>CREATE TABLE `autohome_car_info` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `cityDealerPrice` int(11) unsigned NOT NULL DEFAULT '0',
  `msrpPrice` int(11) unsigned NOT NULL DEFAULT '0',
  `mainBrand` char(20) NOT NULL DEFAULT '',
  `subBrand` varchar(20) NOT NULL DEFAULT '',
  `brandSerie` varchar(20) NOT NULL DEFAULT '',
  `brandSerieId` varchar(15) NOT NULL DEFAULT '',
  `model` varchar(50) NOT NULL DEFAULT '',
  `modelId` varchar(15) NOT NULL DEFAULT '',
  `modelStatus` char(5) NOT NULL DEFAULT '',
  `url` varchar(200) NOT NULL DEFAULT '',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
</code>

当然测试期间,可以去改个table 的名字,避免把之前的冲突了

参考:

去搜搜:

IF NOT EXISTS

mysql IF NOT EXISTS

MySQL: Insert record if not exists in table – Stack Overflow

MySQL :: MySQL 5.5 Reference Manual :: 17.4.1.6 Replication of CREATE … IF NOT EXISTS Statements

MYSQL IF NOT EXISTS AND IF EXISTS – CSDN博客

mysql CREATE table IF NOT EXISTS

MySQL :: MySQL 8.0 Reference Manual :: 13.1.18 CREATE TABLE Syntax

MySQL对CREATE TABLE IF NOT EXISTS SELECT的处理 – CSDN博客

然后创建数据库的代码:

<code>#!/usr/bin/env python
# -*- encoding: utf-8 -*-
# Author: Crifan Li
# Project: autohomeBrandData
# Function: implement custom result worker for autohome car data

import pymysql
import pymysql.cursors


class MysqlDb:
    config = {
        'host': '127.0.0.1',
        'port': 3306,
        'user': 'root',
        'password': 'crifan_mysql',
        'database': 'AutohomeResultdb',
        'charset': "utf8"
    }

    connection = None
    isConnected = False

    def __init__(self):
        """init mysql"""
        if not self.isConnected:
            self.isConnected = self.connect()
            print("Connect mysql return", self.isConnected)

    def connect(self):
        try:
            self.connection = pymysql.connect(**self.config, cursorclass=pymysql.cursors.DictCursor)
            print("connect mysql ok, self.connection=", self.connection)
            return True
        except pymysql.Error as err:
            print("Connect mysql with config=", self.config, " error=", err)
            return False

    def createTable(self, newTablename):
        if self.connection is None:
            print("Please connect mysql first before create table")
            return False

        cursor = self.connection.cursor()
        print("cursor=", cursor)

        createTableSql = """CREATE TABLE `%s` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `cityDealerPrice` int(11) unsigned NOT NULL DEFAULT '0',
  `msrpPrice` int(11) unsigned NOT NULL DEFAULT '0',
  `mainBrand` char(20) NOT NULL DEFAULT '',
  `subBrand` varchar(20) NOT NULL DEFAULT '',
  `brandSerie` varchar(20) NOT NULL DEFAULT '',
  `brandSerieId` varchar(15) NOT NULL DEFAULT '',
  `model` varchar(50) NOT NULL DEFAULT '',
  `modelId` varchar(15) NOT NULL DEFAULT '',
  `modelStatus` char(5) NOT NULL DEFAULT '',
  `url` varchar(200) NOT NULL DEFAULT '',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;""" % (newTablename)
        print("createTableSql=", createTableSql)

        try:
            cursor.execute(createTableSql)
            self.connection.commit()
            return True
        except pymysql.Error as err:
            print("Create table using sql=", createTableSql, "error=", err)
            return False

def test():
    """test mysql"""
    mysqlObj = MysqlDb()
    print("mysqlObj=", mysqlObj)

    # testTablename = "autohome_car_info"
    testTablename = "tbl_car_info_test"
    createTableOk = mysqlObj.createTable(testTablename)
    print("createTable", testTablename, "return", createTableOk)

if __name__ == '__main__':
    test()

</code>

运行正常:

去看看,果然创建了数据库了:

接着去drop table

调试期间,用sql:

<code>CREATE TABLE `%s` IF NOT EXISTS (
</code>

结果出错:

<code>error= (1064, "You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'IF NOT EXISTS (\n  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,\n  `cityDealerPr' at line 1")
</code>

发现是自己写错位置了,换成:

<code>CREATE TABLE IF NOT EXISTS `%s` (
</code>

就可以了。

不过对于,去创建已经存在的table的话,会出现warning:

<code>/Users/crifan/.virtualenvs/AutocarData-xI-iqIq4/lib/python3.6/site-packages/pymysql/cursors.py:322: Warning: (1050, "Table 'tbl_car_info_test' already exists")
  self._do_get_result()
</code>

不过去加了:

<code>except pymysql.Warning as warn:
    print("Create table using sql=", createTableSql, "Warning=", warn)
    return True
</code>

却没有执行到。

所以去掉吧。

然后就可以正常的继续去drop掉table了:

然后用代码:

<code>
# def insert(self, **valueDict):
def insert(self, tablename, valueDict):
    """
        inset dict value into mysql table
        makesure the value is dict, and its keys is the key in the table
    """
    if self.connection is None:
        print("Please connect mysql first before insert value into table")
        return False

    cursor = self.connection.cursor()
    print("cursor=", cursor)

    dictKeyList = valueDict.keys()
    dictValueList = valueDict.values()
    print("dictKeyList=", dictKeyList, "dictValueList=", dictValueList)

    keyListSql = ", ".join(self.quoteIdentifier(eachKey) for eachKey in dictKeyList)
    print("keyListSql=", keyListSql)
    # valueListSql = ", ".join(eachValue for eachValue in dictValueList)
    valueListSql = ""
    formattedDictValueList = []
    for eachValue in dictValueList:
        print("eachValue=", eachValue)
        eachValueInSql = ""
        valueType = type(eachValue)
        print("valueType=", valueType)
        if valueType is str:
            eachValueInSql = '"%s"' % eachValue
        elif valueType is int:
            eachValueInSql = '%d' % eachValue
        # TODO: add more type formatting if necessary
        print("eachValueInSql=", eachValueInSql)
        formattedDictValueList.append(eachValueInSql)

    valueListSql = ", ".join(eachValue for eachValue in formattedDictValueList)
    print("valueListSql=", valueListSql)

    # insertSql = """INSERT INTO %s (id, url, title, type, thumb, count, temperature, images, tags, post_time) values (%s,%s,%s,%s,%s,%s,%s,%s,%s)"""
    insertSql = """INSERT INTO %s (%s) VALUES (%s)""" % (tablename, keyListSql, valueListSql)
    print("insertSql=", insertSql)

    try:
        cursor.execute(insertSql)
        self.connection.commit()
        return True
    except pymysql.Error as err:
        print("Insert into table using sql=", insertSql, "error=", err)
        return False


</code>

# 4. test insert value dict

valueDict = {

    “url”: “https://www.autohome.com.cn/spec/5872/#pvareaid=2042128”, #车型url

    “mainBrand”: “宝马”, #品牌

    “subBrand”: “华晨宝马”, #子品牌

    “brandSerie”: “宝马3系”, #车系

    “brandSerieId”: “66”, #车系ID

    “model”: “2010款 320i 豪华型”, #车型

    “modelId”: “5872”, #车型ID

    “modelStatus”: “停售”, #车型状态

    “cityDealerPrice”: 325000, #经销商参考价

    “msrpPrice”: 375000 # 厂商指导价

}

print(“valueDict=”, valueDict)

insertOk = mysqlObj.insert(testTablename, valueDict)

print(“insertOk=”, insertOk)

可以正常的插入值:

【总结】

最后用代码:

<code>#!/usr/bin/env python
# -*- encoding: utf-8 -*-
# Project: autohomeBrandData
# Function: implement custom result worker for autohome car data
# Author: Crifan Li
# Date: 20180512
# Note:
#   If you want to modify to your mysql and table, you need:
#   (1) change change MysqlDb config to your mysql config
#   (2) change CurrentTableName to your table name
#   (3) change CreateTableSqlTemplate to your sql to create new mysql table fields
#   (4) before use this ResultWorker, run py file to execute testMysqlDb, to init db and create table
#   (5) if your table field contain more type, edit insert to add more type for "TODO: add more type formatting if necessary"


import pymysql
import pymysql.cursors

CurrentTableName = "tbl_autohome_car_info"
CreateTableSqlTemplate = """CREATE TABLE IF NOT EXISTS `%s` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT COMMENT '自增,主键',
  `cityDealerPrice` int(11) unsigned NOT NULL DEFAULT '0' COMMENT '经销商参考价',
  `msrpPrice` int(11) unsigned NOT NULL DEFAULT '0' COMMENT '厂商指导价',
  `mainBrand` char(20) NOT NULL DEFAULT '' COMMENT '品牌',
  `subBrand` varchar(20) NOT NULL DEFAULT '' COMMENT '子品牌',
  `brandSerie` varchar(20) NOT NULL DEFAULT '' COMMENT '车系',
  `brandSerieId` varchar(15) NOT NULL DEFAULT '' COMMENT '车系ID',
  `model` varchar(50) NOT NULL DEFAULT '' COMMENT '车型',
  `modelId` varchar(15) NOT NULL DEFAULT '' COMMENT '车型ID',
  `modelStatus` char(5) NOT NULL DEFAULT '' COMMENT '车型状态',
  `url` varchar(200) NOT NULL DEFAULT '' COMMENT '车型url',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;"""

class MysqlDb:
    config = {
        'host': '127.0.0.1',
        'port': 3306,
        'user': 'root',
        'password': 'crifan_mysql',
        'database': 'AutohomeResultdb',
        'charset': "utf8"
    }

    defaultTableName = CurrentTableName
    connection = None

    def __init__(self):
        """init mysql"""
        # 1. connect db first
        if self.connection is None:
            isConnected = self.connect()
            print("Connect mysql return %s" % isConnected)

        # 2. create table for db
        createTableOk = self.createTable(self.defaultTableName)
        print("Create table %s return %s" %(self.defaultTableName, createTableOk))

    def connect(self):
        try:
            self.connection = pymysql.connect(**self.config, cursorclass=pymysql.cursors.DictCursor)
            print("connect mysql ok, self.connection=", self.connection)
            return True
        except pymysql.Error as err:
            print("Connect mysql with config=", self.config, " error=", err)
            return False

    def quoteIdentifier(self, identifier):
        """
            for mysql, it better to quote identifier xxx using backticks to `xxx`
            in case, identifier:
                contain special char, such as space
                or same with system reserved words, like select
        """
        quotedIdentifier = "`%s`" % identifier
        # print("quotedIdentifier=", quotedIdentifier)
        return quotedIdentifier

    def executeSql(self, sqlStr, actionDescription=""):
        print("executeSql: sqlStr=%s, actionDescription=%s" % (sqlStr, actionDescription))

        if self.connection is None:
            print("Please connect mysql first before %s" % actionDescription)
            return False

        cursor = self.connection.cursor()
        print("cursor=", cursor)

        try:
            cursor.execute(sqlStr)
self.connection.commit()
            return True
        except pymysql.Error as err:
            print("Execute sql %s occur error %s for %s" % (sqlStr, err, actionDescription))
            return False

    def createTable(self, newTablename):
        print("createTable: newTablename=", newTablename)

        createTableSql = CreateTableSqlTemplate % (newTablename)
        print("createTableSql=", createTableSql)

        return self.executeSql(sqlStr=createTableSql, actionDescription=("Create table %s" % newTablename))

    def dropTable(self, existedTablename):
        print("dropTable: existedTablename=", existedTablename)

        dropTableSql = "DROP TABLE IF EXISTS %s" % (existedTablename)
        print("dropTableSql=", dropTableSql)

        return self.executeSql(sqlStr=dropTableSql, actionDescription=("Drop table %s" % existedTablename))

    # def insert(self, **valueDict):
    def insert(self, valueDict, tablename=defaultTableName):
        """
            inset dict value into mysql table
            makesure the value is dict, and its keys is the key in the table
        """
        print("insert: valueDict=%s, tablename=%s" % (valueDict, tablename))

        dictKeyList = valueDict.keys()
        dictValueList = valueDict.values()
        print("dictKeyList=", dictKeyList, "dictValueList=", dictValueList)

        keyListSql = ", ".join(self.quoteIdentifier(eachKey) for eachKey in dictKeyList)
        print("keyListSql=", keyListSql)
        # valueListSql = ", ".join(eachValue for eachValue in dictValueList)
        valueListSql = ""
        formattedDictValueList = []
        for eachValue in dictValueList:
            # print("eachValue=", eachValue)
            eachValueInSql = ""
            valueType = type(eachValue)
            # print("valueType=", valueType)
            if valueType is str:
                eachValueInSql = '"%s"' % eachValue
            elif valueType is int:
                eachValueInSql = '%d' % eachValue
            # TODO: add more type formatting if necessary
            print("eachValueInSql=", eachValueInSql)
            formattedDictValueList.append(eachValueInSql)

        valueListSql = ", ".join(eachValue for eachValue in formattedDictValueList)
        print("valueListSql=", valueListSql)

        insertSql = """INSERT INTO %s (%s) VALUES (%s)""" % (tablename, keyListSql, valueListSql)
        print("insertSql=", insertSql)
        # INSERT INTO tbl_car_info_test (`url`, `mainBrand`, `subBrand`, `brandSerie`, `brandSerieId`, `model`, `modelId`, `modelStatus`, `cityDealerPrice`, `msrpPrice`) VALUES ("https://www.autohome.com.cn/spec/5872/#pvareaid=2042128", "宝马", "华晨宝马", "宝马3系", "66", "2010款 320i 豪华型", "5872", "停售", 325000, 375000)

        return self.executeSql(sqlStr=insertSql, actionDescription=("Insert value to table %s" % tablename))

    def delete(self, modelId, tablename=defaultTableName):
        """
            delete item from car model id for existing table of autohome car info
        """
        print("delete: modelId=%s, tablename=%s" % (modelId, tablename))

        deleteSql = """DELETE FROM %s WHERE modelId = %s""" % (tablename, modelId)
        print("deleteSql=", deleteSql)

        return self.executeSql(sqlStr=deleteSql, actionDescription=("Delete value from table %s by model id %s" % (tablename, modelId)))

def testMysqlDb():
    """test mysql"""

    testDropTable = True
    testCreateTable = True
    testInsertValue = True
    testDeleteValue = True

    # 1.test connect mysql
    mysqlObj = MysqlDb()
    print("mysqlObj=", mysqlObj)

    # testTablename = "autohome_car_info"
    # testTablename = "tbl_car_info_test"
    testTablename = CurrentTableName
    print("testTablename=", testTablename)

    if testDropTable:
        # 2. test drop table
        dropTableOk = mysqlObj.dropTable(testTablename)
        print("dropTable", testTablename, "return", dropTableOk)

    if testCreateTable:
        # 3. test create table
        createTableOk = mysqlObj.createTable(testTablename)
        print("createTable", testTablename, "return", createTableOk)

    if testInsertValue:
        # 4. test insert value dict
        valueDict = {
            "url": "https://www.autohome.com.cn/spec/5872/#pvareaid=2042128", #车型url
            "mainBrand": "宝马", #品牌
            "subBrand": "华晨宝马", #子品牌
            "brandSerie": "宝马3系", #车系
            "brandSerieId": "66", #车系ID
            "model": "2010款 320i 豪华型", #车型
            "modelId": "5872", #车型ID
            "modelStatus": "停售", #车型状态
            "cityDealerPrice": 325000, #经销商参考价
            "msrpPrice": 375000 # 厂商指导价
        }
        print("valueDict=", valueDict)
        insertOk = mysqlObj.insert(valueDict=valueDict, tablename=testTablename)
        print("insertOk=", insertOk)

    if testDeleteValue:
        toDeleteModelId = "5872"
        deleteOk = mysqlObj.delete(modelId=toDeleteModelId, tablename=testTablename)
        print("deleteOk=", deleteOk)

if __name__ == '__main__':
    testMysqlDb()
</code>

可以实现在Python中去操作mysql了。

转载请注明:在路上 » 【已解决】Python中如何操作mysql

发表我的评论
取消评论

表情

Hi,您需要填写昵称和邮箱!

  • 昵称 (必填)
  • 邮箱 (必填)
  • 网址
81 queries in 0.166 seconds, using 22.15MB memory