Python操作mysql

此脚本是执行后台自动发布每日一图

#coding=utf-8
import MySQLdb
import datetime
import time
import re

TIME_FORMAT_0 = '%Y%m%d'
sql_contents="insert into typecho_contents(cid,title,slug,created,modified,text,authorId,allowComment,allowPing,allowFeed) values(%s,%s,%s,%s,%s,%s,%s,%s,%s,%s)"
sql_fields='insert into typecho_fields(cid,name,type,str_value) values(%s,%s,%s,%s)'
sql_relationships = 'insert into typecho_relationships(cid,mid) values(%s,%s)'

today = datetime.datetime.now()
today_format = today.strftime(TIME_FORMAT_0)

timestamp = int(time.time())

#获取最新cid
def get_last_cid():
    try:
        last_one = cur.execute("select cid from typecho_contents order by cid desc limit 1")
    except Exception as e:
        print ('执行%s出错:%s'%(sql_relationships, e))

    array = cur.fetchone()
    return array[0]

def get_thumb_index():
    last_one = cur.execute("select str_value from typecho_fields where name='thumb' order by cid desc limit 1")
    result = cur.fetchone()
    pattern = re.compile(r"\/\d+")
    match = pattern.search(result[0])

    if match:
        return match.group[1:]

    return 0


def insert_post(cid,title,slug,created,modified,text,authorId):
    try:
        cur.execute(sql_contents, (cid,title,slug,created,modified,text,authorId,1,1,1))
    except Exception as e:
        print ('执行%s出错:%s'%(sql_contents, e))

    return

def insert_fields(_cid,_name,_type,_str_value):
    try:
        cur.execute(sql_fields, (_cid,_name,_type,_str_value))
    except Exception as e:
        print ('执行%s出错:%s'%(sql_fields, e))

    return

def insert_relation(cid, mid):
    try:
        cur.execute(sql_relationships, (cid, mid))
    except Exception as e:
        print ('执行%s出错:%s'%(sql_relationships, e))
    return

if __name__== '__main__':
    conn= MySQLdb.connect(host='127.0.0.1', port = 1111, user='user', passwd='passwd', db='db', charset='utf8',)
    cur = conn.cursor()

    cid = get_last_cid() + 1

    title = ('%s%s'%('每日一图', today_format))
    slug = ('%s%s'%('img_', today_format))
    img_url = ("http://kun775.oss-cn-shenzhen.aliyuncs.com/img/%s.jpg"%(today_format))
    text = ("<!--markdown--><img style='width:100%%;' src=%s /> \
    \n此图由后台自动发布。\
    \n图片来源：<a target='_blank' href='http://cn.bing.com/'>必应</a>"%(img_url))

    print (cid)
    print (title)
    print (slug)
    print (text)

    insert_post(cid,title,slug,timestamp,timestamp,text,4)
    insert_fields(cid,'thumb','str', img_url)
    insert_relation(cid, 1)
    insert_relation(cid, 27)

    cur.close()
    conn.commit()
    conn.close()