程序猿 程序猿

Python操作mysql

in 技术教程 阅读[615] ShiKun 文章转载请注明来源!

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

#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()
pythonmysql
最后由ShiKun修改于2018-05-04 13:50
发表新评论
雷姆
拉姆