用pymysql远程加载MySQL数据，遇到的数据表不能写中文问题及解决全过程

最新推荐文章于 2024-06-03 10:53:52 发布
最新推荐文章于 2024-06-03 10:53:52 发布
阅读量699 收藏
点赞数
分类专栏： MySQL 文章标签： 数据库 mysql
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/m0_57021623/article/details/124236980
版权 
# 如何从mysql里查数据
# 安装两个包
#  pip install pymysql
# pip install sqlalchemy

在这里插入图片描述

import numpy as np
import pandas as pd

import pymysql
import sqlalchemy

主要用于查询的数据加载

  • pymysql
  • sqlalchemy

写入数据

  • sqlalchemy
# 连接数据库时要注意：开放3318端口，本次演示关不关防火墙没有影响
# 在服务器安装docker,用docker下拉mysql5.7
# 例如执行  docker run -itd --name db_test -e MYSQL_ROOT_PASSWORD=root -p 3316:3306 mysql:5.7       意思是将3016端口映射到3306上面
#    即 访问3306，就可以连接3306了

遇到数据库连接问题：

2003, "Can't connect to MySQL server on 'xx.xx.xxx.xx' (timed out)

解决思路：
因为是远程连接，首先要考虑服务器的安全组有没有开放所对应的端口，3306是默认开放的，其他端口需要自己进入到服务器页面开放端口。

# 连接数据库
conn =pymysql.Connect(host='xx.xx.xxx.xx',
                      port=3306,
                      user='root',
                      password='root',
                      db='mysql',
                      charset ='utf8'
                     )

# 创建数据库person
with conn.cursor() as c:
    
    c.execute('drop database if exists person')
    c.execute('create database person charset utf8')


with conn.cursor() as c:
    
    # 创建表
    c.execute('drop table if exists tb_person')
    sql = """
        create table tb_person(
            id integer primary key auto_increment,
            name varchar(20),
            pwd varchar(20),
            phone varchar(11)
        ) charset =utf8
    
    """
    c.execute(sql)
    sql2 = """
        insert into tb_person(name,pwd,phone)values
        ('A','1','111'),
        ('B','2','222'),
        ('C','3','333')
    
    """
    c.execute(sql2)
    # 提交事务
    conn.commit()

在这里插入图片描述

df = pd.read_sql('select * from tb_person',conn)
df
idnamepwdphone
01A1111
12B2222
23C3333
# 再添加一条
df.loc[3] = [4,'D','4','444']
df
idnamepwdphone
01A1111
12B2222
23C3333
34D4444

在这里插入图片描述

# 存储数据
a = df.to_sql('tb_personl',conn)   # pymyql的连接是不能进行数据的存储
"""
DatabaseError: Execution failed on sql: SELECT name FROM sqlite_master WHERE type='table' AND name=?;
not all arguments converted during string formatting
unable to rollback
"""

# 关闭链接
conn.close()

# sqlalchemy.engine.create_engine()
"""
engine = create_engine("mysql://scott:tiger@hostname/dbname",
                                encoding='latin1', echo=True)
"""

'\nengine = create_engine("mysql://scott:tiger@hostname/dbname",\n                                encoding=\'latin1\', echo=True)\n'

db_url = 'mysql+pymysql://root:root@xx.xx.xxx.xx:3306/person'
conn = sqlalchemy.engine.create_engine(db_url,encoding='utf8',echo=True)

# 读取
pd.read_sql('select * from tb_person1',conn)

2022-04-17 21:24:24,171 INFO sqlalchemy.engine.base.Engine DESCRIBE `select * from tb_person1`
2022-04-17 21:24:24,172 INFO sqlalchemy.engine.base.Engine {}
2022-04-17 21:24:44,076 INFO sqlalchemy.engine.base.Engine select * from tb_person1
2022-04-17 21:24:44,079 INFO sqlalchemy.engine.base.Engine {}
idnamepwdphone
01A1111
12B2222
23C3333
34D4444
45E5555
56王二5555

写入数据

  • sqlalchemy
# 写入数据
df.to_sql('tb_person1',conn)
# df.to_sql('tb_person1',conn,index=False) 不存索引
# df.to_sql('tb_person1',conn,index=False,if_exists='replace') 不存索引,如果存在，替换掉

2022-04-17 20:52:59,242 INFO sqlalchemy.engine.base.Engine SHOW VARIABLES LIKE 'sql_mode'
2022-04-17 20:52:59,250 INFO sqlalchemy.engine.base.Engine {}
2022-04-17 20:52:59,303 INFO sqlalchemy.engine.base.Engine SHOW VARIABLES LIKE 'lower_case_table_names'
2022-04-17 20:52:59,304 INFO sqlalchemy.engine.base.Engine {}
2022-04-17 20:52:59,361 INFO sqlalchemy.engine.base.Engine SELECT DATABASE()
2022-04-17 20:52:59,362 INFO sqlalchemy.engine.base.Engine {}
2022-04-17 20:52:59,416 INFO sqlalchemy.engine.base.Engine show collation where `Charset` = 'utf8mb4' and `Collation` = 'utf8mb4_bin'
2022-04-17 20:52:59,417 INFO sqlalchemy.engine.base.Engine {}
2022-04-17 20:52:59,446 INFO sqlalchemy.engine.base.Engine SELECT CAST('test plain returns' AS CHAR(60)) AS anon_1
2022-04-17 20:52:59,447 INFO sqlalchemy.engine.base.Engine {}
2022-04-17 20:52:59,478 INFO sqlalchemy.engine.base.Engine SELECT CAST('test unicode returns' AS CHAR(60)) AS anon_1
2022-04-17 20:52:59,479 INFO sqlalchemy.engine.base.Engine {}
2022-04-17 20:52:59,507 INFO sqlalchemy.engine.base.Engine SELECT CAST('test collated returns' AS CHAR CHARACTER SET utf8mb4) COLLATE utf8mb4_bin AS anon_1
2022-04-17 20:52:59,508 INFO sqlalchemy.engine.base.Engine {}
2022-04-17 20:52:59,593 INFO sqlalchemy.engine.base.Engine DESCRIBE `tb_person1`
2022-04-17 20:52:59,595 INFO sqlalchemy.engine.base.Engine {}
2022-04-17 20:52:59,623 INFO sqlalchemy.engine.base.Engine ROLLBACK
2022-04-17 20:52:59,680 INFO sqlalchemy.engine.base.Engine 
CREATE TABLE tb_person1 (
	`index` BIGINT, 
	id BIGINT, 
	name TEXT, 
	pwd TEXT, 
	phone TEXT
)


2022-04-17 20:52:59,680 INFO sqlalchemy.engine.base.Engine {}
2022-04-17 20:52:59,729 INFO sqlalchemy.engine.base.Engine COMMIT
2022-04-17 20:52:59,757 INFO sqlalchemy.engine.base.Engine CREATE INDEX ix_tb_person1_index ON tb_person1 (`index`)
2022-04-17 20:52:59,758 INFO sqlalchemy.engine.base.Engine {}
2022-04-17 20:52:59,816 INFO sqlalchemy.engine.base.Engine COMMIT
2022-04-17 20:52:59,879 INFO sqlalchemy.engine.base.Engine BEGIN (implicit)
2022-04-17 20:52:59,883 INFO sqlalchemy.engine.base.Engine INSERT INTO tb_person1 (`index`, id, name, pwd, phone) VALUES (%(index)s, %(id)s, %(name)s, %(pwd)s, %(phone)s)
2022-04-17 20:52:59,885 INFO sqlalchemy.engine.base.Engine ({'index': 0, 'id': 1, 'name': 'A', 'pwd': '1', 'phone': '111'}, {'index': 1, 'id': 2, 'name': 'B', 'pwd': '2', 'phone': '222'}, {'index': 2, 'id': 3, 'name': 'C', 'pwd': '3', 'phone': '333'}, {'index': 3, 'id': 4, 'name': 'D', 'pwd': '4', 'phone': '444'})
2022-04-17 20:52:59,917 INFO sqlalchemy.engine.base.Engine COMMIT

# 再添加一条数据
df.loc[4] = [5,'E','5','555']
df.to_sql('tb_person1',conn,index=False,if_exists='replace')

2022-04-17 21:00:39,107 INFO sqlalchemy.engine.base.Engine DESCRIBE `tb_person1`
2022-04-17 21:00:39,108 INFO sqlalchemy.engine.base.Engine {}
2022-04-17 21:00:39,166 INFO sqlalchemy.engine.base.Engine DESCRIBE `tb_person1`
2022-04-17 21:00:39,167 INFO sqlalchemy.engine.base.Engine {}
2022-04-17 21:00:39,221 INFO sqlalchemy.engine.base.Engine SHOW FULL TABLES FROM `person`
2022-04-17 21:00:39,223 INFO sqlalchemy.engine.base.Engine {}
2022-04-17 21:00:39,286 INFO sqlalchemy.engine.base.Engine SHOW CREATE TABLE `tb_person1`
2022-04-17 21:00:39,287 INFO sqlalchemy.engine.base.Engine {}
2022-04-17 21:00:39,376 INFO sqlalchemy.engine.base.Engine 
DROP TABLE tb_person1
2022-04-17 21:00:39,379 INFO sqlalchemy.engine.base.Engine {}
2022-04-17 21:00:39,418 INFO sqlalchemy.engine.base.Engine COMMIT
2022-04-17 21:00:39,479 INFO sqlalchemy.engine.base.Engine 
CREATE TABLE tb_person1 (
	id BIGINT, 
	name TEXT, 
	pwd TEXT, 
	phone TEXT
)


2022-04-17 21:00:39,480 INFO sqlalchemy.engine.base.Engine {}
2022-04-17 21:00:39,532 INFO sqlalchemy.engine.base.Engine COMMIT
2022-04-17 21:00:39,619 INFO sqlalchemy.engine.base.Engine BEGIN (implicit)
2022-04-17 21:00:39,622 INFO sqlalchemy.engine.base.Engine INSERT INTO tb_person1 (id, name, pwd, phone) VALUES (%(id)s, %(name)s, %(pwd)s, %(phone)s)
2022-04-17 21:00:39,624 INFO sqlalchemy.engine.base.Engine ({'id': 1, 'name': 'A', 'pwd': '1', 'phone': '111'}, {'id': 2, 'name': 'B', 'pwd': '2', 'phone': '222'}, {'id': 3, 'name': 'C', 'pwd': '3', 'phone': '333'}, {'id': 4, 'name': 'D', 'pwd': '4', 'phone': '444'}, {'id': 5, 'name': 'E', 'pwd': '5', 'phone': '555'})
2022-04-17 21:00:39,654 INFO sqlalchemy.engine.base.Engine COMMIT

# 再添加一条中文的
df.loc[5] = [6,'王二','5','555']
df.to_sql('tb_person1',conn,index=False,if_exists='replace')

2022-04-17 21:03:11,677 INFO sqlalchemy.engine.base.Engine DESCRIBE `tb_person1`
2022-04-17 21:03:11,678 INFO sqlalchemy.engine.base.Engine {}
2022-04-17 21:03:11,736 INFO sqlalchemy.engine.base.Engine DESCRIBE `tb_person1`
2022-04-17 21:03:11,737 INFO sqlalchemy.engine.base.Engine {}
2022-04-17 21:03:11,792 INFO sqlalchemy.engine.base.Engine SHOW FULL TABLES FROM `person`
2022-04-17 21:03:11,795 INFO sqlalchemy.engine.base.Engine {}
2022-04-17 21:03:11,827 INFO sqlalchemy.engine.base.Engine SHOW CREATE TABLE `tb_person1`
2022-04-17 21:03:11,829 INFO sqlalchemy.engine.base.Engine {}
2022-04-17 21:03:11,898 INFO sqlalchemy.engine.base.Engine 
DROP TABLE tb_person1
2022-04-17 21:03:11,899 INFO sqlalchemy.engine.base.Engine {}
2022-04-17 21:03:11,938 INFO sqlalchemy.engine.base.Engine COMMIT
2022-04-17 21:03:11,998 INFO sqlalchemy.engine.base.Engine 
CREATE TABLE tb_person1 (
	id BIGINT, 
	name TEXT, 
	pwd TEXT, 
	phone TEXT
)


2022-04-17 21:03:12,001 INFO sqlalchemy.engine.base.Engine {}
2022-04-17 21:03:12,048 INFO sqlalchemy.engine.base.Engine COMMIT
2022-04-17 21:03:12,103 INFO sqlalchemy.engine.base.Engine BEGIN (implicit)
2022-04-17 21:03:12,106 INFO sqlalchemy.engine.base.Engine INSERT INTO tb_person1 (id, name, pwd, phone) VALUES (%(id)s, %(name)s, %(pwd)s, %(phone)s)
2022-04-17 21:03:12,108 INFO sqlalchemy.engine.base.Engine ({'id': 1, 'name': 'A', 'pwd': '1', 'phone': '111'}, {'id': 2, 'name': 'B', 'pwd': '2', 'phone': '222'}, {'id': 3, 'name': 'C', 'pwd': '3', 'phone': '333'}, {'id': 4, 'name': 'D', 'pwd': '4', 'phone': '444'}, {'id': 5, 'name': 'E', 'pwd': '5', 'phone': '555'}, {'id': 6, 'name': '王二', 'pwd': '5', 'phone': '555'})
2022-04-17 21:03:12,138 INFO sqlalchemy.engine.base.Engine COMMIT

此时：
在这里插入图片描述
如何让数据表显示中文呢？

1.用bash的方式进容器 docker exec -it db_test bash
2.进入到mysql的配置文件 cd mysql
3.进到mysql.conf.d目录，修改mysqld.cnf文件
4.把这句话追加到文件里 
echo 'character-set-server=utf8'  >> mysqld.conf
5.进入到conf.d目录，修改mysql.cnf文件
6.把这句话追加到文件里
echo 'default-character-set=utf8' >> mysql.cnf
7.然后 exit 退出
8.重启数据库docker restart db_test
9.查看日志docker logs db_test
10.查看数据表

在这里插入图片描述
在这里插入图片描述
在这里插入图片描述
在这里插入图片描述

在这里插入图片描述
此时：

show variables like 'character%';

在这里插入图片描述

今晚务必早点睡
关注
  • 0
    点赞
  • 踩
  • 0
    收藏
    觉得还不错? 一键收藏
  • 打赏
    打赏
  • 0
    评论
专栏目录
pandas中read_sql(read_sql_query)中文字符乱码问题
Great haste makes great waste
11-07 1万+
使用pandas读取数据库时，由于存储有汉字，开始读取时发现汉字被乱码显示，如下：In [70]: import pymysqlIn [71]: conn = pymysql.connect(host='localhost', port=3306, user='root', password='123456', db='test')In [72]: import pandas as pdIn [73
python连接本地mysql_python连接mysql的两种方式
weixin_42456278的博客
02-04 760
1.方式一(推荐)import pymysqlfrom sqlalchemy import create_enginepymysql.install_as_MySQLdb()engine = create_engine(“mysql+pymysql://root:*****@localhost:3306/test?charset=gbk”)sql = “select * from student”...
DatabaseError: Execution failed on sql ‘SELECT name FROM sqlite_master WHERE type=‘table’ AND -九五小庞
最新发布
qq_37241964的博客
06-03 535
查了pandas官网和其他不少有关to_sql的文章，都没有看到有提及数据库连接这个坑，不过我注意到，pandas官网的to_sql例子和其他文章的to_sql例子都有一个共同点，就是引用sqlalchemy这个第三方ORM库来进行连接的，然后我依样画葫芦，终于解决to_sql这个报错。df.to_sql(‘test’,conn,index=False’) # 然后写进数据库，坑这时出现了，就是conn导致的，你怎么都没想到，conn在read_sql中能正常读取，在to_sql中却报错。
ORM
weixin_42737944的博客
11-01 161
ORM ORM,对象关系映射，对象和关系之间的映射，使用面向对象的方式来操作数据库。 关系模型和Python对象之间的映射 table => class ,表映射为类 row => object ,行映射为实例 column => property ,字段映射为属性 SQLALchemy SQLALchemy是一个ORM框架 安装 pip install sqla...
Pandas to_sql详解
简介不重要
06-29 5133
源码： def to_sql( self, name, con, schema=None, if_exists="fail", index=True, index_label=None, chunksize=None, dtype=None, ...
Python中Flask框架SQLALCHEMY_ECHO设置
weixin_30713953的博客
12-27 1160
在用配置类的方式给app设置配置时,SQLALCHEMY_ECHO 这个是记录打印SQL语句用于调试的, 一般设置为False, 不然会在控制台输出一大堆的东西 /home/python/.virtualenvs/py3_flask/bin/python /home/python/Desktop/test/demo_sqlalchemy.py2018-12-27 17:46:54,20...
pymysql通过读取mysql临时表数据更新或插入主表
04-03
pymysql+mysql通过读取临时表数据更新或插入主表，原创手写已测可运行效果好。
解决pymysql cursor.fetchall() 获取不到数据的问题
12-17
1.之前的写法（不报错）： data = cursor.fetchall() data_name = data[0][‘task_type’] 2.简洁的写法（报错）： data = cursor.fetchall()...他和变量不一样，不能重复查询，推荐第一种写法，将数据取出来之后，
Pymysql实现往表中插入数据过程解析
09-08
总结来说，使用Pymysql库向MySQL表中插入数据的过程包括：连接数据库、创建游标、编写SQL语句、使用`executemany()`方法执行SQL、处理事务以及关闭连接。这个过程适用于任何需要在Python中与MySQL进行交互的场景，为...
在python中使用pymysql往mysql数据库中插入(insert)数据实例
09-08
本文将详细介绍如何使用pymysql来实现数据的插入操作，并结合一个实际示例来解释可能遇到的问题及其解决方案。 首先，我们需要了解基本的步骤： 1. **创建连接**：使用`connect()`函数创建一个到MySQL服务器的连接...
pymysql报错: unable to rollback、Already closed
计算机辅助工程
12-06 1043
【代码】pymysql查询: unable to rollback。
python操作数据库出现错误 : Unknown column 'XXXX' in 'where clause'"
bluewater的专栏
04-17 4846
第一种情况:Unknown column 'XXXX' in 'where clausepython操作数据库出现错误 : Unknown column 'XXXX' in 'where clause'")错误 - CSDN博客  https://blog.csdn.net/you_are_my_dream/article/details/53180474在MySQL数据库中存在中文字符，pyth...
《Flask入门》第五章：学flask-sqlalchemy看着一篇就够了，别整多的
09年小盆友滴博客
06-14 571
《Flask入门》第五章：保姆级的flask-sqlalchemy使用教程 依我个人的理解，数据库就是存储数据和数据与数据与数据直接的关系的工具，无论他存储的数据的复杂程度或者数据的大小，他跟一个整齐的房间没有本质上的区别。数据库的东西可以在通过身份验证后容易的获取和修改，并且速度也很关键。今天，我来说说如何在flask中用flask-sqlalchemy优雅的使用mysql ...
实用：python中sqlalchemy数据库框架+pymysql操作mysql数据库
Mankey_24的博客
08-03 338
import sqlalchemy from sqlalchemy.ext.declarative import declarative_base from sqlalchemy import Column,Integer,String #实体基类 Base = declarative_base() #实体类 class Student(Base): __tablename__ = '...
sqlalchemy(二)高级用法
weixin_34122548的博客
05-20 354
本文将介绍sqlalchemy的高级用法。 外键以及relationship 首先创建数据库，在这里一个user对应多个address，因此需要在address上增加user_id这个外键（一对多）。 #!/usr/bin/env python # encoding: utf-8 from sqlalchemy import create_eng...
SQLAlchemy使用教程
山间明月江上清风的专栏
05-06 797
SQLAlchemy是python的ORM框架，把表映射成对象，类似于java的hibernate，mybatis。 而且支持的数据库非常多： Firebird Microsoft SQL Server MySQL Oracle PostgreSQL SQLite Sybase 还支持其他数据库扩展。 下面来正式使用，首先基于之前文章在intellij idea+python plu...
专栏：012：没时间解释了，快使用sqlalchemy
IAlexanderI的专栏
04-10 353
崇尚的学习思维是：输入，输出平衡，且平衡点不断攀升。 今天的主题是：sqlalchemy的使用(这是一篇没有真正实战的博文) 0：框架 序号 内容 说明 01 概念解释 是什么？ 02 代码解释 怎么做？ 03 总结 如何做的？ 1：概念 ORM对象关系映射（英语：Object Relational Mapping，简...
实用：python中sqlalchemy中mysql数据库的简单查询和复杂查询
Mankey_24的博客
08-04 1049
import sqlalchemy from sqlalchemy.ext.declarative import declarative_base from sqlalchemy import Column,Integer,String,inspect,DATE,Enum,and_,or_,not_ from sqlalchemy.orm import sessionmaker Base = d...
Sqlalchemy中文编码问题
qq_30818049的博客
06-14 1154
issue python下sqlalchemy操作mysql数据库存在中文编码问题 create_engine中已经设定了encoding=“utf-8”，但还是出现编码问题："UnicodeEncodeError: ‘latin-1’ codec can’t encode characters in position 0-3: "，问题的根源在于mysql创建数据库默认为latin-1。 修改...
pymysql远程连接MySQL
05-17
要使用 PyMySQL 进行远程连接 MySQL 数据库，需要进行以下步骤： 1. 确认远程 MySQL 服务器允许远程访问。 2. 安装 PyMySQL 模块：`pip install pymysql` 3. 使用以下代码进行连接： ```python import pymysql # ...

“相关推荐”对你有帮助么？

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包

打赏作者

今晚务必早点睡

你的鼓励将是我创作的最大动力

¥1 ¥2 ¥4 ¥6 ¥10 ¥20
扫码支付：¥1
获取中
扫码支付

您的余额不足，请更换扫码支付或充值

打赏作者

实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值