python 更新插入mySQL数据库

最新推荐文章于 2025-07-16 17:57:01 发布

逍遥_yjz

最新推荐文章于 2025-07-16 17:57:01 发布

阅读量4.3k

点赞数

CC 4.0 BY-SA版权

分类专栏： python基础 Python-主流库

本文链接：https://blog.youkuaiyun.com/xiaoyaozizai017/article/details/75041200

python基础同时被 2 个专栏收录

24 篇文章

订阅专栏

Python-主流库

2 篇文章

订阅专栏

本文介绍了一种将贴吧数据批量导入数据库的方法，并通过具体实例演示了如何使用Python和MySQL连接器实现这一过程。文章详细解释了从文件读取数据到执行SQL插入语句的每一步操作，对于需要处理大量文本数据并将其结构化存储的技术人员来说，是一个实用的参考案例。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

#-*- 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 ='http://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()