Python采集学习笔记-MySQL不同表的数据转移

最新推荐文章于 2024-08-23 09:00:40 发布

DevCodeMemo

最新推荐文章于 2024-08-23 09:00:40 发布

阅读量434

点赞数 10

文章标签：数据库 python 学习

本文链接：https://blog.csdn.net/2301_77321248/article/details/135885070

版权

(同一数据库表数据转移)源码

import mysql.connector

# 连接到MySQL数据库
conn = mysql.connector.connect(
    host='localhost',
    user='root',
    password='pwd_Admin',
    database='tlias'
)

# 创建游标对象
cursor = conn.cursor()

try:
    # 查询emp表中的前10条数据
    query = "SELECT id,username,password,name,gender,image,job,entrydate,dept_id FROM emp LIMIT 10"
    cursor.execute(query)
    result = cursor.fetchall()

    # 将数据插入到emp_copy表中
    for row in result:
        insert_query = "INSERT INTO emp_copy (id,username,password,name,gender,image,job,entrydate,dept_id) VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s)"
        cursor.execute(insert_query, row)

    # 提交事务
    conn.commit()
    print("数据插入成功！")

except mysql.connector.Error as error:
    print("数据插入失败: {}".format(error))

finally:
    # 关闭游标和数据库连接
    cursor.close()
    conn.close()

(不同数据库表数据转移)源码

import mysql.connector

def connect_db(host, user, password, database):
    conn = mysql.connector.connect(
        host=host,
        user=user,
        password=password,
        database=database
    )
    return conn

def db_read(conn, table):
    cursor = conn.cursor()
    try:
        # 查询emp表中的所有数据
        query = f"SELECT id, username, password, name, gender, image, job, entrydate, dept_id FROM {table}"
        cursor.execute(query)
        result = cursor.fetchall()
        return result
    except mysql.connector.Error as error:
        print("数据读取失败: {}".format(error))

def db_write(conn, result, table):
    cursor = conn.cursor()
    try:
        # 将数据插入到emp_copy表中
        insert_query = f"INSERT INTO {table} (id, username, password, name, gender, image, job, entrydate, dept_id) VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s)"
        cursor.executemany(insert_query, result)
        # 提交事务
        conn.commit()
        print("数据插入成功！")

    except mysql.connector.Error as error:
        print("数据插入失败: {}".format(error))
    cursor.close()
    conn.close()

if __name__ == '__main__':
    # 连接到tlias数据库
    conn_t = connect_db('localhost', 'root', 'pwd_Admin', 'tlias')
    result = db_read(conn_t, 'emp')
    print(result)

    # 连接到test数据库
    conn_d = connect_db('localhost', 'root', 'pwd_Admin', 'test')
    db_write(conn_d, result, 'emp_copy')