typecho在用pymysql-MariaDB的时候插入数据遇到的问题及其解决

发表于 分类于 本文字数： 4.3k 阅读时长 ≈ 4 分钟

程序环境

  • 运行环境：
  • 树莓派: Linux 5.4.51-v7
  • nginx: 1.14.2
  • MariaDB: 10.3.23
  • typecho: 1.1(17.10.30)
  • 开发环境：
  • pymysql: 0.10.0
  • python：3.6.8

    问题及解决

  1. typecho的内容表为 typecho_contents, 表设计如下：
    | Field | Type | Null | Key | Default | Extra
    | — | — | — | — | —-| —| —|
    |cid | int(10) unsigned | NO | PRI | NULL | auto_increment |
    | title | varchar(200) | YES | | NULL | |
    | slug | varchar(200) | YES | UNI | NULL | |
    | created | int(10) unsigned | YES | MUL | 0 | |
    | modified | int(10) unsigned | YES | | 0 | |
    | text | longtext | YES | | NULL | |
    | order | int(10) unsigned | YES | | 0 | |
    | authorId | int(10) unsigned | YES | | 0 | |
    | template | varchar(32) | YES | | NULL | |
    | type | varchar(16) | YES | | post | |
    | status | varchar(16) | YES | |publish | |
    | password | varchar(32) | YES | | NULL | |
    | commentsNum | int(10) unsigned | YES | | 0 | |
    | allowComment | char(1) | YES | | 0 | |
    | allowPing | char(1) | YES | | 0 | |
    | allowFeed | char(1) | YES | | 0 | |
    | parent | int(10) unsigned | YES | | 0 | |
    | views | int(10) | YES | | 0 | |
  2. 遇到的问题
    在用pymysql访问MariaDB的时候，前期的connect，cursor都是正常的，查询也没有问题，但是在插入数据的时候，遇到了幺蛾子：
  • 如果对typecho_contents进行全表全字段覆盖式的插入：
  • 不指名字段名字，可以插入成功
    1
    INSERT INTO typecho_contents  VALUES ( (select Auto_increment From information_schema.Tables where Table_Schema="typechoblog" AND table_name = "typecho_contents"), '测试数据TITLE', (select Auto_increment From information_schema.Tables where Table_Schema="typechoblog" AND table_name = "typecho_contents"), 1599480000, 1599480000, '测试数据内容', 0, 1, '', 'post', 'publish', '',1, '1', '1', '1', 0, 200);
  • 指名字段名字，插入失败
    1
    INSERT INTO typecho_contents(cid, title, slug, created, modified, text, order, authorId, template, type, status, password, commentsNum, allowComment, allowPing, allowFeed, parent, views)  VALUES ( (select Auto_increment From information_schema.Tables where Table_Schema="typechoblog" AND table_name = "typecho_contents"), '测试数据III', (select Auto_increment From information_schema.Tables where Table_Schema="typechoblog" AND table_name = "typecho_contents"), 1599480000, 1599480000, '这个只是一个测试，看看情况再说2', 0, 1, '', 'post', 'publish', '',101, '1', '1', '1', 0, 200);
    把SQL语句直接放到MariaDB的shell环境也一样, 出错信息：
    1
    ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'order, authorId, template, type, status, password, commentsNum, allowComment,...' at line 1
  • 如果对typecho_contents指定字段插入:
  • 用SET，可以插入成功
    1
    2
    3
    4
    sql = "INSERT INTO typecho_contents SET title=‘%s’, slug=%s, created=%s, modified=%s, text=‘%s’, authorId=%s"
    param = (blog.title, cid, created, created, text, authorId)
    sql = sql % param
    cursor.execute(sql)
  • 用VALUES，插入失败
    用法和上面全表全字段插入的方式一样，就是VALUES后面显示写明字段名

    最终代码

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16
    17
    18
    19
    20
    21
    22
    23
    24
    25
    26
    27
    28
    29
    30
    31
    32
    33
    34
    35
    36
    37
    38
    39
    40
    41
    42
    43
    44
    import pymysql, time
    from ConfigData import ConfigData as cd
    from Blog import Blog
    def makeTypechoData(blog):
    cid =  '(select Auto_increment From information_schema.Tables where Table_Schema="typechoblog" AND table_name = "typecho_contents")'
    created = int(time.mktime(time.strptime(blog.publish, cd.timeFormat)))
    text = "<!--markdown-->" + blog.content
    order = 0
    authorId = 1
    template = ''
    ctype = 'post'
    status = 'publish'
    password = ''
    commentsNum = int(blog.comment)
    allowComment = '1'
    allowPing = '1'
    allowFeed = '1'
    parent = 0
    views = int(blog.read)
    conn = pymysql.Connect(
    host = cd.db_host,
    port = cd.db_port,
    user = cd.db_user,
    passwd = cd.db_password,
    db = cd.db_name,
    charset = cd.db_charset
    )
    cursor = conn.cursor()
    try:
    sql = "INSERT INTO typecho_contents SET title='%s', slug=%s, created=%s, modified=%s, text='%s', authorId=%s, commentsNum=%s, views=%s"
    param = (blog.title, cid, created, created, text, authorId, commentsNum, views)
    sql = sql % param
    print(sql)
    cursor.execute(sql)
    conn.commit()
    except Exception as e:
    conn.rollback()
    print(e)
    finally:
    cursor.close()
    conn.close()
    if __name__ == '__main__':
    blog = Blog('2020-09测试数据TITLE','http', '2020-09-08 10:00:00', 10, 20, [], '#### 标题1 #### 标题2', [], [])
    makeTypechoData(blog)
    除了SQL之外，里面还有两点需要注意的：
  1. 是关于自增字段取值的，slug自动保持和cid一样，所以，取得当前记录的自增记录的语句为：
    1
    cid =  '(select Auto_increment From information_schema.Tables where Table_Schema="typechoblog" AND table_name = "typecho_contents")'
  2. 是关于python时间处理的
    1
    created = int(time.mktime(time.strptime(blog.publish, cd.timeFormat)))`
    具体就不多写了，看python的time模块注意mktime和strptime的用法就行