当前位置： 移动技术网 > IT编程>脚本编程>Python > python更新插入mySQL数据库教程

python更新插入mySQL数据库教程

2018年04月11日  | 移动技术网IT编程  | 我要评论

国营东方仪器厂,耀扬711来福枪,疑似邓世昌印章出水

python更新插入mySQL数据库教程

#-*- coding:utf-8 -*-

import sys
import mysql.connector
import chardet
import datetime
import codecs


reload(sys)
sys.setdefaultencoding('utf-8')
collection_status =0
created_time = datetime.datetime.now().strftime("%Y-%m-%d %H:%M:%S")
#查看是否插入主题事件的表中
def testInsertIntoTable():
    print '开始'
    db = mysql.connector.connect(host='182.00.00.00', port='3306', user='root', password='*****',
                                 database='university_event_analyse', use_unicode=True)
    cursor = db.cursor()

    url ='https://tieba.baidu.com/p/5097201091'
    theme ='有没有今年被石大录取的研究生呀？'
    main_view ='有的话认识一下呗'
    follow_count =34
    post_type = '校园环境'
    created_time =datetime.datetime.now().strftime("%Y-%m-%d %H:%M:%S")
    #print type(created_time)
    source ='贴吧'

    post_time ='2017-05-01 19:21'

    sql = "INSERT INTO daily_event(url,theme, main_view, follow_count, \
                         post_type,created_time,source,collection_status,post_time) \
                   VALUES ('%s','%s', '%s', '%d', '%s', '%s', '%s','%d','%s')" % \
          (url,theme, main_view, follow_count,post_type,created_time,source,collection_status,post_time)
    try:
        cursor.execute(sql)
        db.commit()
    except:
        db.rollback()

    db.close()

#testInsertIntoTable()

#测试把文本数据插入数据库
def testTextInsertIntoTable():
    print '开始'
    db = mysql.connector.connect(host='182.00.00.00', port='3306', user='root', password='*****',
                                 database='university_event_analyse', use_unicode=True)
    cursor = db.cursor()

    fr = codecs.open(r'D:\PythonFiles\teiba\new\finallyData\testData\20170614.event', 'rb', 'utf-8')
    r_data = fr.readlines()

    for line in r_data:
        if line.strip() != '':
            line_list = line.strip().split('\001')

            url = line_list[6]
            theme = line_list[0]
            main_view = line_list[1]
            follow_count = line_list[2]
            post_type = line_list[3]

            source = line_list[5]

            post_time = line_list[4]
            #print url
            sql_query = "SELECT * FROM daily_event \
                        WHERE url = '%s'" % url
            sql = "INSERT INTO daily_event(url,theme, main_view, follow_count, \
                                     post_type,created_time,source,collection_status,post_time) \
                               VALUES ('%s','%s', '%s', '%d', '%s', '%s', '%s','%d','%s')" % \
                  (url, theme, main_view, int(follow_count), post_type, created_time, source, collection_status, post_time)
            sql_update = "UPDATE  daily_event SET theme= '%s' , main_view = '%s', follow_count = '%d',post_type = '%s', \
                    created_time ='%s', source = '%s',collection_status = '%d',post_time = '%s'" % (theme, main_view, int(follow_count), post_type, created_time, source, collection_status,post_time)+"where url = '%s'" % url

            try:
                #cursor.execute(sql)
                cursor.execute(sql_query)
                results = cursor.fetchall()
                if len(results):
                    # print len(results)
                    # print results
                    # print results[0][1]
                    cursor.execute(sql_update)
                    db.commit()
                else:
                    #print results[0][1]
                    print 'nida'
                    cursor.execute(sql)
                    db.commit()
            except:
                db.rollback()


    db.close()

testTextInsertIntoTable()

您可能感兴趣的文章:

如对本文有疑问，请在下面进行留言讨论，广大热心网友会与你互动！！ 点击进行留言回复

相关文章:

验证码：
最近更新的文章
大家感兴趣的文章
移动技术网