import pymysql
from myconfig import host, user, password, database
# 连接数据库
try:
connection = pymysql.connect(
host= host,
user= user,
password= password,
database= database,
charset='utf8mb4',
cursorclass=pymysql.cursors.DictCursor
)
print("连接成功")
# connection.close()
except pymysql.Error as e:
print(f"连接失败: {e}")连接成功import pandas as pd
cursor = connection.cursor()
# SQL 查询语句
query_table_sql = f'''
SELECT table_name
FROM information_schema.columns
WHERE table_schema = '{database}'
AND table_name LIKE '%硫辛酸%';
'''
# 执行查询
cursor.execute(query_table_sql)
# 获取结果
results = cursor.fetchall()
pd.DataFrame(results)| TABLE_NAME | |
|---|---|
| 0 | 硫辛酸crf |
| 1 | 硫辛酸crf |
| 2 | 硫辛酸crf |
| 3 | 硫辛酸crf |
| 4 | 硫辛酸研究随机表 |
| 5 | 硫辛酸研究随机表 |
| 6 | 硫辛酸研究随机表 |
| 7 | 硫辛酸研究随机表 |
| 8 | 硫辛酸研究随机表 |
| 9 | 硫辛酸研究随机表 |
| 10 | 硫辛酸研究随机表 |
| 11 | 硫辛酸研究随机表 |
| 12 | 硫辛酸研究随机表 |
| 13 | 硫辛酸研究随机表 |
| 14 | 硫辛酸研究随机表 |
query_inclusion_sql = f'''
SELECT patID as 住院号,
patName as 姓名,
随机日期 as 日期
FROM `硫辛酸研究随机表`
WHERE used = 1;
'''
# 执行查询
cursor.execute(query_inclusion_sql)
# 获取结果
results = cursor.fetchall()
df = pd.DataFrame(results)
df.head()| 住院号 | 姓名 | 日期 | |
|---|---|---|---|
| 0 | 756605 | 黎辉 | 0000-00-00 00:00:00 |
| 1 | 713203 | 梁秀莲 | 0000-00-00 00:00:00 |
| 2 | 774504 | 朱百臻 | 0000-00-00 00:00:00 |
| 3 | 895615 | 杨华锋 | 0000-00-00 00:00:00 |
| 4 | 230331 | 陈富禄 | 0000-00-00 00:00:00 |
以将远程数据库迁移至本地为例
import pymysql
databaseParam_local = ("200.128.103.26", "root", "", "hulinhui") # 本地
host, user, passwd, db = databaseParam_local
try:
conn = pymysql.connect(host=host, user=user, passwd=passwd, db=db)
print("连接成功")
except pymysql.Error as e:
print(f"连接失败：{e}")报错： 连接失败：(1130, "ZZSK-54' is not allowed to connect to this MariaDB server") 解决：
200.128.103.26ping ipaddress
C:\Users\admin>ping 200.128.103.26响应内容为：
正在 Ping 200.128.103.26 具有 32 字节的数据:
来自 200.128.103.26 的回复: 字节=32 时间=1ms TTL=128
来自 200.128.103.26 的回复: 字节=32 时间=1ms TTL=128
来自 200.128.103.26 的回复: 字节=32 时间=1ms TTL=128
来自 200.128.103.26 的回复: 字节=32 时间=1ms TTL=128
200.128.103.26 的 Ping 统计信息:
数据包: 已发送 = 4，已接收 = 4，丢失 = 0 (0% 丢失)，
往返行程的估计时间(以毫秒为单位):
最短 = 1ms，最长 = 1ms，平均 = 1ms以上响应结果说明可以成功连接至200.128.103.26
确保MariaDB服务器上的主机防火墙规则允许3306端口。
netstat -ano | findstr :3306响应内容为：
TCP 0.0.0.0:3306 0.0.0.0:0 LISTENING 10088
TCP 200.128.103.31:63094 200.128.103.26:3306 TIME_WAIT 0
TCP 200.128.103.31:63100 200.128.103.26:3306 TIME_WAIT 0
TCP [::]:3306 [::]:0 LISTENING 10088说明3306端口正常开放。
SELECT User, Host From mysql.user;返回结果为：
| User | Host |
|---|---|
| root | 127.0.0.1 |
| root | ::1 |
| pma | localhost |
| root | localhost |
可见，需要新建用户，sql语句格式为：
GRANT ALL PRIVILEGES ON *.* TO 'username'@'ZZSK-54' IDENTIFIED BY 'password';
FLUSH PRIVILEGES;GRANT ALL PRIVILEGES ON *.* TO 'hulinhui'@'ZZSK-54' IDENTIFIED BY 'Excuseme2';
FLUSH PRIVILEGES;
GRANT ALL PRIVILEGES ON *.* TO 'root'@'ZZSK-54' IDENTIFIED BY '';
FLUSH PRIVILEGES;Excuseme2是密码。重新连接成功！
写入
migrate-database.py文件，执行python migrate-database.py即可
# import pymysql
# databaseParam_remote = ("200.128.103.26", "root", "", "hulinhui") # 远程
# host, user, passwd, db = databaseParam_remote
# try:
# conn_remote = pymysql.connect(host=host, user=user, passwd=passwd, db=db)
# print("连接远程数据库成功")
# except pymysql.Error as e:
# print(f"连接远程数据库失败：{e}")
# databaseParam_local = ("localhost", "root", "", "hulinhui") # 本地
# host, user, passwd, db = databaseParam_local
# try:
# conn_local = pymysql.connect(host=host, user=user, passwd=passwd, db=db)
# print("连接本地数据库成功")
# except pymysql.Error as e:
# print(f"连接本地数据库失败：{e}")
import pymysql
# 设置支持执行多条命令，否则pymysql会报语法错误。
client_flag = pymysql.constants.CLIENT.MULTI_STATEMENTS
remote_config = {
'host' : '200.128.103.26',
'user' : 'root',
'password' : '',
'db' : 'hulinhui'
}
local_config = {
'host' : 'localhost',
'user' : 'root',
'password' : '',
'db' : 'hulinhui'
}
# 连接到远程数据库获取表结构
with pymysql.connect(**remote_config) as remote_conn:
with remote_conn.cursor() as cursor:
# 获取所有表名
cursor.execute("SHOW TABLES")
table_names = [row[0] for row in cursor.fetchall()]
# 创建表名与表创建语句的字典
table_create_sql_dict = {}
for table_name in table_names:
# 获取表的创建语句
cursor.execute(f"SHOW CREATE TABLE {table_name}")
table_create_sql = cursor.fetchone()[1]
table_create_sql_dict[table_name] = table_create_sql
# print(f"table_create_sql: {table_create_sql}")
# print(f"table_create_sql_dict: {table_create_sql_dict}"")
# 连接到要地数据库并创建表
with pymysql.connect(**local_config, client_flag = client_flag) as local_conn: # with自动关闭local_conn
with local_conn.cursor() as cursor:
for table_name, table_create_sql in table_create_sql_dict.items():
# 在本地创建与远程表结构相同的表
create_table_query = f"DROP TABLE IF EXISTS `{table_name}`; {table_create_sql}"
# print(f"\n\ncreate_table_query: {create_table_query}")
cursor.execute(create_table_query)
local_conn.commit()
print(f"远程数据库的{table_name}表结构已成功复制至本地数据库")
print(f"远程数据库的所有表结构已成功复制至本地数据库")
# 将远程数据库的数据表的数据逐条插入至本地数据库的数据表
for table_name in table_names:
with pymysql.connect(**remote_config) as remote_conn:
with remote_conn.cursor() as cursor:
# 复制数据，每100条数据作为一个批次
cursor.execute(f"SELECT * FROM {table_name}")
rows = cursor.fetchall()
# print(rows)
with pymysql.connect(**local_config, client_flag = client_flag) as local_conn: # with自动关闭local_conn
with local_conn.cursor() as cursor:
# 批量插入数据
values_placeholders = ','.join(['%s'] * len(rows[0]))
insert_sql = f"INSERT INTO {table_name} VALUES ({values_placeholders})"
# print(insert_sql)
cursor.executemany(insert_sql, rows)
# 提交事务，使插入生效
local_conn.commit()
print(f"远程数据库的{table_name}表数据已成功复制至本地数据库")
print(f"远程数据库的所有表数据已成功复制至本地数据库")说明：目前数据量的情况下不用分批处理，迁移的速度还算快，大概1分半钟左右。
通过http://localhost/phpmyadmin/查看数据库。