python读取文件内容并写入远端mysql数据库

最新推荐文章于 2025-06-25 15:20:07 发布

原创最新推荐文章于 2025-06-25 15:20:07 发布 · 2.2k 阅读

9 ·

CC 4.0 BY-SA版权

python 同时被 2 个专栏收录

4 篇文章

订阅专栏

mysql

1 篇文章

订阅专栏

本文介绍了一个使用 Python 连接 MySQL 数据库并执行数据写入操作的示例程序。该程序首先定义了一个 MysqlServer 类用于处理数据库连接及 SQL 执行，然后通过 read 方法读取指定文件的内容，并将其作为评论数据插入到数据库中。

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

#!/usr/bin/python
# -*- coding: UTF-8 -*-
import MySQLdb
import os


class MysqlServer(object):
    def __init__(self, host, port, user, password):
        self.host = host
        self.port = port
        self.user = user
        self.passwd = password
        self.conn = self.__create_connection()

    def __create_connection(self):
        #和远端mysql建立连接
        conn = MySQLdb.connect(host=self.host, port=self.port, user=self.user, passwd=self.passwd, charset='utf8')
        #选取数据库
        conn.select_db("db_name")
        return conn

    def close(self):
        #关闭连接
        self.conn.close()

    def execute(self, sql):
        #执行sql语句
        cursor = self.conn.cursor()
        cursor.execute(sql)
        self.conn.commit()


def get_comment_data_info(file_path):
    #获取文件内容
    with open(file_path, 'r') as file:
        comment_data_info = file.read()
    return comment_data_info


def write_data_to_db(data):
    #判断文件是否存在
    if not os.path.exists(data['data_info']['file_path']):
        return "file: %s not exist" % data['data_info']['file_path']
    comment_data_info = get_comment_data_info(data['data_info']['file_path'])

    #生成sql语句
    sql = "INSERT INTO user_info(username, course_id, score, comment) VALUES " "('%s', '%s', '%d', '%s')" % \
          (data['data_info']['username'], data['data_info']['course_id'], data['data_info']['score'], comment_data_info)

    try:
        mysql_server = MysqlServer(data['db_info']['host'], data['db_info']['port'], data['db_info']['user'],
                                   data['db_info']['passwd'])
        mysql_server.execute(sql)
    except Exception as e:
        return str(e)
    return "insert success"


def main():
    data = {
        #远端数据库信息
        "db_info": {"host": "192.168.100.100",
                    "port": 3306,
                    "user": "root",
                    "passwd": "abcdefg", },
        #需要插入的信息以及文件路径
        "data_info": {"username": "user1",
                      "course_id": "100",
                      "score": 100,
                      "file_path": "/test1233"}
    }
    status = write_data_to_db(data)
    print(status)


if __name__ == "__main__":
    main()