python学习中遇到的坑（1）

1、

pandas的to_sql写入数据库，如何操作？根据pandas文档，可以在执行to_sql方法时，将映射好列名和指定类型的dict赋值给dtype参数即可上，其中对于MySQL表的列类型可以使用SQLAlchemy包中封装好的类型。

from sqlalchemy.types import NVARCHAR, Float, Integer
dtypedict = {
  'str': NVARCHAR(length=255),
  'int': Integer(),
  'float' Float()
}
df.to_sql(name='test', con=con, if_exists='append', index=False, dtype=dtypedict)

但看网上人不建议用dtype格式指定数据类型，建议先用数据库sql脚本创建好表结构，然后再用to_sql控制入库,关键这样好控制入库的数据格式。今天写完sql脚本后，放到虚拟机里执行时，报错

ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'NOT NULL COMMENT '流水号',
  `czm_1` char(60) DEFAULT NULL COMMENT '场站名' at line 3

很纳闷，查了半天有说是符号有问题的，有说最后建表语句没有分号结尾的，我在nodepad++中，用符号展示功能检查好一会，没发现问题。回头又看了下报错信息，将除了主键key之外的字段的定一部分的NOT NULL去掉，改外DEFAULT NULL后，重新放入脚本执行成功。似乎只有主键字段才能设置NOT NULL？这里还不是很清楚。

https://www.cnblogs.com/jzxy/articles/15996865.html，根据这篇文章，NULL 其实并不是空值，而是要占用空间，所以mysql在进行比较的时候，NULL 会参与字段比较，所以对效率有一部分影响。

2、

https://blog.csdn.net/mzl_18353516147/article/details/89552667

使用pymysql连接mysql库写入数据时，报错[WinError 10061] 由于目标计算机积极拒绝，无法连接。我查了下，原来是要修改系统的代理设置，修改完之后pip下载成功。

入库时，报错index字段多余无法入库。具体代码：

def sql_write():
    target_Data = data_clean()
    engine = create_engine("mysql+mysqlconnector://xx:xxxx@xxx.xx.xxx.xxx:3306/aaa?charset=utf8")
    con = engine.connect()
    target_Data.to_sql(name='pro_name_table', con=con, if_exists='append')
    con.close()

原来以为是拼接df的原因，发现非此原因。to_sql()参数中除 name、con必填外，可选参数index推荐使用False，同时dtype推荐不使用。查阅文档，to_sql()参数中，index字段默认是True，也即在写入库时，会将默认索引也作为独立列写入数据表，正常是不用的。需要注明：

def sql_write():
    target_Data = data_clean()
    engine = create_engine("mysql+mysqlconnector://xx:xxxx@xxx.xx.xxx.xxx:3306/aaa?charset=utf8")
    con = engine.connect()
    target_Data.to_sql(name='pro_name_table', con=con, index=False, if_exists='append')
    con.close()

之后入库无问题。

3、

https://www.jianshu.com/p/4c5e1ebe8470 参考此文，通过指定dtype 参数值来改变数据库中创建表的列类型。

可以用自己写映射函数的方式，将DataFrame中列名和指定类型映射：

def mapping_df_types(df):
    dtypedict = {}
    for i, j in zip(df.columns, df.dtypes):
        if "object" in str(j):
            dtypedict.update({i: NVARCHAR(length=255)})
        if "float" in str(j):
            dtypedict.update({i: Float(precision=2, asdecimal=True)})
        if "int" in str(j):
            dtypedict.update({i: Integer()})
    return dtypedict

df = pd.DataFrame([['a', 1, 1, 2.0, datetime.now(), True]], 
                  columns=['str', 'int', 'float', 'datetime', 'boolean'])
dtypedict = mapping_df_types(df)
df.to_sql(name='test', con=con, if_exists='append', index=False, dtype=dtypedict)

只要在执行to_sql前使用此方法获得一个映射dict再赋值给to_sql的dtype参数即可。

查询mysql数据库表，结果格式化输出有三种方法：% , f"{a}" , "{}".format(a)。（参考链接：https://www.jianshu.com/p/d4f444cb0ce0）

4、

关于django与flask如何选择哪个，作为前端页面展示的web开发框架？参考https://www.cnblogs.com/liuyangQAQ/p/14762448.html文章，对比决定选用flask，小型、轻量、灵活性强，倾向于选择Flask来开发小型或静态网站，或者在实现快速交付RESTful API Web服务时选择。本身我是自用网站，用不到太大的应用，快速、轻量能满足要求即可。该文作者建议：从Flask开始，在获得一些Web开发经验后再转到Django会更容易一些。我综合考虑，决定用flask框架。

5、

python3中pymysql与SQLAlchemy的区别。pymysql是直接操作mysql数据库，SQLAlchemy通过ORM操作数据库。

pymysql是通过连接对象的方法去获取游标对象cursor，以此来操作数据库。SQLAlchemy通过引擎创建数据库会话，然后创建ORM模型对数据库操作。我选用SQLAlchemy来操作数据库。

SQLAlchemy操作可参见https://www.cnblogs.com/jclian91/p/12121735.html。接下来学习下SQLAlchemy的具体操作函数。

engine是SQLAlchemy 中位于数据库驱动之上的一个抽象概念，它适配了各种数据库驱动，提供了连接池等功能。其用法就是 如上面例子中，engine = create_engine(<数据库连接串>)，数据库连接串的格式是 dialect+driver://username:password@host:port/database?参数 这样的，dialect 可以是 mysql, postgresql, oracle, mssql, sqlite，后面的 driver 是驱动，比如MySQL的驱动pymysql， 如果不填写，就使用默认驱动。再往后就是用户名、密码、地址、端口、数据库、连接参数了。

Session的意思就是会话，也就是说，是一个逻辑组织的概念，因此，这需要靠你的业务逻辑来划分哪些操作使用同一个Session， 哪些操作又划分为不同的业务操作。举个简单的例子，以web应用为例，一个请求里共用一个Session就是一个好的例子，一个异步任务执行过程中使用一个Session也是一个例子。 但是注意，不能直接使用Session，而是使用Session的实例。

在尝试继续编写读取数据展示的小程序，具体sqlalchemy对已有数据库的数据读取，可参考orm操作：https://blog.csdn.net/weixin_44504761/article/details/122257465，更复杂点的orm操作学习此文章：https://blog.csdn.net/u012335228/article/details/98402399与https://blog.csdn.net/weixin_46281427/article/details/122916870。

具体sqlalchemy中，union组合查询与union all组合的示例如下：

# 组合
q1 = session.query(Users.name).filter(Users.id > 2)
q2 = session.query(Favor.caption).filter(Favor.nid < 2)
ret = q1.union(q2).all()  #数据重复，只留一条
 
q1 = session.query(Users.name).filter(Users.id > 2)
q2 = session.query(Favor.caption).filter(Favor.nid < 2)
ret = q1.union_all(q2).all()  #显示数据重复

 https://blog.csdn.net/nico2333/article/details/104196289，该文中提到：如果把数据库中已有的表映射为Model进行操作时，需要注意：，每一属性特性必须都列出来，比如说，数据库表中各列的数据类型、是否为主键、是否为空等等。此文对于sqlalchemy对已有数据库的映射操作写的很详细，可以参考一二。其中一段写的很好，会话部分：

@contextlib.contextmanager
def get_session():
    s = sessionmaker(bind=engine)
    try:
        yield s
        s.commit()
    except Exception as e:
        s.rollback()
        raise e
    finally:
        s.close()

如果用flask框架展示数据库表信息，建议用flask-SQLAlchemy查询展示。需要生成sqlalchemy使用的连接url，并创建MySQL的ORM对象并反射数据库中已存在的表，获取所有存在的表对象，https://blog.csdn.net/weixin_40238625/article/details/88177492，https://blog.csdn.net/qq_34964418/article/details/105392170，https://blog.csdn.net/lllzg000/article/details/121781296。

6、 20221205

今天遇到的问题是（1）如何用python读取mysql数据表，并展示数据在flask网页上。（2）可用的搜索选项是flask+读mysql+表格。（3）还有一种可能性，用flask+pyqt5实现。

不过看知乎上有人有同样问题，大佬回复：

直接Flask开发把，前端用Vue.js，因为你以后这种小项目很多，前后端隔离，复用性提升，后面改起来也容易，除非是很复杂的界面，PyQt5或者PySide2都没必要。

即使是小软件，前后端分离，Web化也是趋势，而且甚至可以说，现在连后端渲染Template也没太大必要，FLASK只需要提供REST API就可以了，前端的页面完全用VUE.JS渲染就好了。

7、

尝试使用sqlacodegen反射mysql表生成models的过程，https://blog.csdn.net/wnx_52055/article/details/93480769，因为我也碰到了提示mysqldb module不存在的报错，按照文章链接中的方法，修改配置后成功。

8、

B站视频是强大的获取学习资源的地方，12月9日晚上，对sqlalchemy如何实现查询，参照原本有位UP主的方法，用flask-sqlalchemy库链接读取，但是报错上下文运行错误，查了半天，因为对flask-sqlchemy及flask不熟悉，所以不明白错误在哪里。查了下官方文档，有提到：

作为 Connection 表示针对数据库的开放资源，我们希望始终将此对象的使用范围限制在特定上下文中，最好的方法是使用Python上下文管理器表单，也称为 the with statement .

后用另一位up示例的方法，改用sqlalchemy库链接查询，成功。现在问题如何将查询结果格式化转换出来，参考链接：https://blog.csdn.net/human_soul/article/details/115396697。该文讲解了flask框架中将查询结果格式化输出的方法。

# coding=utf-8
from bottle import route, jinja2_template
from apps.model import Users
from global_conf.settings import db
from sqlalchemy import func


@route('/', method=['GET', 'POST'])
def index():
    users = db.query(Users.username, Users.password, Users.remarks, func.date_format(Users.create_time, "%Y-%m-%d %H:%i:%s")).all()
    return {'u': list(users)}

将时间格式化

func.date_format(Users.create_time, "%Y-%m-%d %H:%i:%s")

 9、

查看sqlalchemy文档中，有这样一段代码：

>>> with engine.connect() as conn:
...     result = conn.execute(text("SELECT x, y FROM some_table"))
...     for row in result:
...         print(f"x: {row.x}  y: {row.y}")

查了下解释，print字符串前面加f表示格式化字符串，加f后可以在字符串里面使用用花括号括起来的变量和表达式，如果字符串里面没有表达式，那么前面加不加f输出应该都一样。

格式化的字符串文字前缀为’f’和接受的格式字符串相似str.format()。

如果用不确定条件如何查询？

可以选择format拼接sql语句（https://www.jianshu.com/p/6d2e780399cd）或，用orm框架实现不确定条件查询（https://www.jianshu.com/p/a33f48387efa）。

10、

对于大文件的增量数据更新，可以用python中文件句柄的方式获取上次读取位置并记录，继续增量可以使用生成器确保只有在数据被调用时才会生成。

知乎上关于增量更新数据的简介，可以参照：https://zhuanlan.zhihu.com/p/514895527。

读取excel表每天将新增数据写入，可以用pandas.shape[1]获取行数量，继而实现增量更新。搜索关键字：增量数据+存储+python，当然更专业的搜索关键词：python+数据变化捕获。数据变化捕获，即CDC考虑增量数据抽取。

找到一个pandas的第三方库datacompy，专门比较两个列一致的dataframe的行数据是否一致的神器。可以解决增量数据筛选问题，筛选出来之后就可以着手，根据索引id将旧数据删除，将新数据新增进去了。可以参考的链接：https://blog.csdn.net/LlanyW/article/details/127472542，以及https://www.cnblogs.com/liuxuelin/p/14767358.html。安装使用简单操作可以参考：https://blog.csdn.net/wxfighting/article/details/123807396。

看到一个大佬写的对比两个列表的数据如何比对，方法记录如下：

#encoding=utf-8
a=[1,2,3,4,5,6,7,8,9,10]
c=[1,2,3,4,5,6,8,7]
b=[1,22,'3',4,5,6,7,8]
#print(a[2:-2])
def List_Cp_List(list1,list2):
    if list1==list2:
        return {"status": True, "message": [u"两个列表中数据一致！"]}
    elif len(list1)==len(list2):
        msg=[]
        for i in range(len(list1)):
            if list1[i]!=list2[i]:
                if type(list1[i])!=type(list2[i]) and str(list1[i])==str(list2[i]):
                    msg.append(u"表I中第%s位元素【%s】的数据类型【%s】!=表II中第%s位【%s】的数据类型【%s】" % (
                    i + 1, list1[i], type(list1[i]), i + 1,list2[i],type(list2[i])))
                else:
                    msg.append(u"表I中第%s位元素【%s】!=表II中第%s位【%s】"%(i+1,list1[i],i+1,list2[i]))
        return {"status": False, "message": msg}
 
    elif len(list1)>len(list2):
        msg=[]
        for i in range(len(list2)):
            if list1[i]!=list2[i]:
                if type(list1[i]) != type(list2[i]) and str(list1[i]) == str(list2[i]):
                    msg.append(u"表I中第%s位元素【%s】的数据类型【%s】!=表II中第%s位【%s】的数据类型【%s】" % (
                    i + 1, list1[i], type(list1[i]), i + 1, list2[i], type(list2[i])))
                else:
                    msg.append(u"表I中第%s位元素【%s】!=表II中第%d位【%s】"%(i+1,list1[i],i+1,list2[i]))
        for i in range(len(list2),len(list1)):
            msg.append(u"表I中第%s位元素【%s】不在表II中" % (i+1,list1[i]))
        return {"status": False, "message": msg}
    else:
        msg=[]
        for i in range(len(list1)):
            if list1[i]!=list2[i]:
                if type(list1[i]) != type(list2[i]) and str(list1[i]) == str(list2[i]):
                    msg.append(u"表I中第%s位元素【%s】的数据类型【%s】!=表II中第%s位【%s】的数据类型【%s】"%(i+1,list1[i],type(list1[i]),i+1,list2[i],type(list2[i])))
                else:
                    msg.append(u"表I中第%d位元素【%s】!=表II中第%d位【%s】"%(i+1,list1[i],i+1,list2[i]))
        for i in range(len(list1),len(list2)):
            msg.append(u"表II中第%s位元素【%s】不在表I中" % (i+1,list2[i]))
        return {"status": False, "message": msg}
 
re=List_Cp_List(b,a)
print re["status"]
for i in re["message"]:
    print i

按列表的序列，依次比对，包括数据的类型都会做比较，当然这不适用超大数据集。单独对比两列数据可以参考，直接用转换为list对比，https://blog.csdn.net/qq_38415758/article/details/109393286