(同一数据库表数据转移)源码
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')