29 篇博文含有标签「Python」

python 时间戳问题

跑程序的时候发现 _id 相同冲突
程序中 mongo_id 的生成有 timestamp 决定
于是测试：

import time    
for i in range(20):    
    print(time.time())    
    
a = time.time()    
for i in range(10**8):    
    time.time()    
print(time.time() - a)    

结果：

1546937536.8363261    
1546937536.8363261    
1546937536.8363261    
1546937536.8363261    
1546937536.8372946    
1546937536.8372946    
1546937536.8372946    
1546937536.8372946    
1546937536.8372946    
1546937536.8372946    
1546937536.8372946    
1546937536.8372946    
1546937536.8372946    
1546937536.8372946    
1546937536.8372946    
1546937536.8372946    
1546937536.8372946    
1546937536.8372946    
1546937536.8372946    
1546937536.8372946    
9.680140972137451  

emmm 经过与同事的机器的对比短时间内
Windows 上时间戳是相同
mac 不同
似乎是系统 bug
各位要用时间戳做 id 的同学注意下
注意
1546937536.8363261
1546937536.8372946
时间不是连续的
time 的精度不同

2021年4月19日Python阅读需 1 分钟

python3 - mysql 数据存储性能测试

要求

一亿数据 10*8
测试用 5w 数据
预测时间为结果时间* 2000

设计思路

程序执行 20 遍求平均值
结束时间 - 开始时间
不同 python 引擎
不同数据量然后 commit 提交响应速度

数据库连接工具

MySQL-Python
pymysql
[X ] MySQL-Connector

代码

    #!/usr/bin/python3    
# encoding: utf-8     
# @Time    : 2018/7/14 0014 16:12    
# @author  : zza    
# @Email   : 740713651@qq.com    
import time    
    
from flask import Flask    
from flask_sqlalchemy import SQLAlchemy    
    
db = SQLAlchemy()    
    
class Student(db.Model):    
    __tablename__ = "stu"    
    id = db.Column(db.Integer, primary_key=True, autoincrement=True, nullable=True)    
    name_ = db.Column(db.String(127))    
    age = db.Column(db.Integer)    
    class_num = db.Column(db.Integer)    
    
def init(param):    
    class sqlalchemy(SQLAlchemy):    
    
        def __del__(self):    
            print("数据库关闭")    
            db.session.close_all()    
    
    app = Flask(__name__)    
    app.config['SQLALCHEMY_DATABASE_URI'] = param + "?charset=utf8&autocommit=False"    
    app.config['SQLALCHEMY_TRACK_MODIFICATIONS'] = True    
    app.config['SQLALCHEMY_POOL_SIZE'] = 128    
    app.config['SQLALCHEMY_POOL_TIMEOUT'] = 60    
    app.config['SQLALCHEMY_POOL_RECYCLE'] = 30    
    app.config['SQLALCHEMY_MAX_OVERFLOW'] = 128    
    # app.config['SQLALCHEMY_ECHO'] = True    
    global db    
    db = sqlalchemy(app)    
    
def finish():    
    db.session.query(Student).delete()    
    db.session.commit()    
    
def time_me(fn):    
    def _wrapper(*args, **kwargs):    
        average = 0    
        i1 = 30    
        seconds = 0    
        for i in range(i1):    
            start = time.time()    
            fn(*args, **kwargs)    
            seconds = time.time() - start    
            average += seconds    
        finish()    
        print(u"{func}函数写入耗时{sec}秒".format(func=fn.__name__, sec=seconds))    
        # print(u"{func}函数每{count}条数数据写入耗时{sec}秒".format(func=fn.__name__, count=args[0], sec=seconds))    
        # finish()    
        return seconds, args    
    
    return _wrapper    
    
@time_me    
def insert_many():    
    # 插入诗句    
    all = 5 * 10 ** 4    
    inner = 1000    
    out = int(all / inner)    
    for i in range(out):    
        for c in range(inner):    
            db.session.add(Student(name_='test mysql insert', age=30, class_num=30))    
        db.session.commit()    
    
######    
@time_me    
def insert_many_by_sql():    
    all = 5 * 10 ** 4    
    inner = 1000    
    out = int(all / inner)    
    with db.session.connection() as con:    
        for i in range(out):    
            for c in range(inner):    
                con.execute(    
                    "INSERT INTO stu ( id ,name_, age, class_num) VALUES (null ,{}, {},{})".format(    
                        "'test2mysql3insert'",    
                        30, 30))    
        db.session.commit()    
    
def main2():    
    """测试 sql 语句与 orm 框架 谁快  包括数据组装"""    
    init("mysql+pymysql://root:root@192.168.14.147:3306/efficiency_test")    
    print("orm 框架插入数据")    
    # iinsert_many 函数每 500 条数数据写入耗时 19.671629905700684 秒    
    insert_many()    
    print("sql 语句插入数据")    
    # insert_many_by_sql 函数每 500 条数数据写入耗时 17.977628707885742 秒    
    insert_many_by_sql()    
    pass    
    
def main():    
    print('测试开始')    
    # insert_many 函数写入耗时 168.07286262512207 秒    
    init("mysql+mysqlconnector://root:root@192.168.14.147:3306/efficiency_test")    
    insert_many()    
    
    # insert_many 函数写入耗时 64.85304117202759 秒    
    init("mysql://root:root@192.168.14.147:3306/efficiency_test")  # 默认使用 MySQLdb    
    insert_many()    
    
    # insert_many 函数写入耗时 64.692676067352295 秒    
    init("mysql+pymysql://root:root@192.168.14.147:3306/efficiency_test")    
    insert_many()    
    
    # insert_many 函数写入耗时 66.991496086120605 秒    
    init("mysql+mysqldb://root:root@192.168.14.147:3306/efficiency_test")    
    insert_many()    
    
if __name__ == '__main__':    
    main()    
    main2()    
    

2021年4月19日Python阅读需 2 分钟

sqlalchemy 多组条件查询

sql 大概是这个样子

select booking_id from booking where concat(num, ',' , name) in ('1,name1', '2,name2', '3,name3')

用sqlalchemy 实现

_list_data = ['1,name1', '2,name2', '3,name3']    
session.query(Booking.booking_id).filter(Booking.num.concat(",").concat(Booking.name).notin_(_list_data))    

2021年4月19日Python阅读需 1 分钟

win10 自带 Ubuntu 使用 win10 的 python 方式

windows cmd 下找到 python 与 pip 文件存放地址

λ  where.exe python    
D:\Python36\python.exe    
    
λ  where.exe pip    
D:\Python36\Scripts\pip.exe    

windows 自带 ubuntu 下（Windows 系统盘符存在/mnt/中）

:~$ sudo ln -s /mnt/d/Python36/Scripts/pip.exe /usr/bin/wpip    
:~$ sudo ln -s /mnt/d/Python36/python.exe /usr/bin/wpython    

2021年4月19日Python阅读需 1 分钟

在控制台（console）执行python文件、debug python程序

执行

在IPython console里先cd到test.py所在的文件夹，再run test.py。注意使用的是命令run 而不是命令python。

debug

栗子：

debugfile('/home/1/tensor2tensor/tensor2tensor/bin/t2t-datagen', args='--problem=image_mnist --data_dir=~/t2t_data --tmp_dir=~/t2t_data/tmp',wdir='/home/1/tensor2tensor/tensor2tensor/bin')

ipython下

cd /home    
pwd    
ls    
help(runfile)    
Help on function runfile in module _pydev_bundle.pydev_umd:    
runfile(filename, args=None, wdir=None, is_module=False, global_vars=None)    
    Run filename    
    args: command line arguments (string)    
    wdir: working directory    
run test.py     

更多资料

2021年4月19日Python阅读需 1 分钟

安装 mysqlclient 报错问题

下载地址

关键字段 mysqlclient python Microsoft Visual Studio 14.0\VC\BIN\cl.exe
解决 musql.c 不存在问题
本身应该在 https://www.lfd.uci.edu/~gohlke/pythonlibs/#mysqlclient 这个网页中下载的
不知道怎么了这个网页中的资源下载不了

error: command 'C:\\Program Files (x86)\\Microsoft Visual Studio 14.0\\VC\\BIN\\cl.exe' failed with exit status 2

注意安装目录不能有中文
版本对应 python 版本
命令为：pip install *.whl

2021年4月19日Python阅读需 1 分钟

用 pycharm 装包出现 pycharm The repository located at pypi douban com is not a

pycharm The repository located at pypi.douban.com is not a trusted or secure

首先在 window 的文件夹窗口输入： %APPDATA%

然后在底下新建 pip 文件夹，然后到 pip 文件夹里面去新建个 pip.ini, 然后再里面输入内容 [global] timeout = 6000 index-url = http://pypi.douban.com/simple trusted-host = pypi.douban.com

作者：摘星辰 Li 链接：https://www.zhihu.com/question/38341743/answer/128985546 来源：知乎

2021年4月19日Python阅读需 1 分钟

解决 Flask-SQLAlchemy 2006 MySQL server has gone away 问题

配置信息

python3 3.6
Flask-SQLAlchemy 2.3.2
win10

报错

-----> [2018-07-16 17:22:42,041] [ERROR] [base.py<131>-base.run_job]: Job "auto_rollback.<locals>.wrapper (trigger: interval[0:30:00], next run at: 2018-07-16 17:52:42 CST)" raised an exception    
Traceback (most recent call last):    
  File "/usr/local/lib/python3.6/site-packages/sqlalchemy/engine/base.py", line 1193, in _execute_context    
    context)    
  File "/usr/local/lib/python3.6/site-packages/sqlalchemy/engine/default.py", line 507, in do_execute    
    cursor.execute(statement, parameters)    
  File "/usr/local/lib/python3.6/site-packages/MySQLdb/cursors.py", line 250, in execute    
    self.errorhandler(self, exc, value)    
  File "/usr/local/lib/python3.6/site-packages/MySQLdb/connections.py", line 50, in defaulterrorhandler    
    raise errorvalue    
  File "/usr/local/lib/python3.6/site-packages/MySQLdb/cursors.py", line 247, in execute    
    res = self._query(query)    
  File "/usr/local/lib/python3.6/site-packages/MySQLdb/cursors.py", line 411, in _query    
    rowcount = self._do_query(q)    
  File "/usr/local/lib/python3.6/site-packages/MySQLdb/cursors.py", line 374, in _do_query    
    db.query(q)    
  File "/usr/local/lib/python3.6/site-packages/MySQLdb/connections.py", line 277, in query    
    _mysql.connection.query(self, query)    
_mysql_exceptions.OperationalError: (2006, 'MySQL server has gone away')    
    
The above exception was the direct cause of the following exception:    
    
Traceback (most recent call last):    
  File "/usr/local/lib/python3.6/site-packages/apscheduler/executors/base.py", line 125, in run_job    
    retval = job.func(*job.args, **job.kwargs)    
  File "/home/zza/eth_crawler/crawler_script/utils.py", line 28, in wrapper    
    raise err    
  File "/home/zza/eth_crawler/crawler_script/utils.py", line 24, in wrapper    
    return func(*args, **kwargs)    
  File "/home/zza/eth_crawler/crawler_script/token_tracker.py", line 240, in update    
    db_address = db.session.query(Token.contract_address).filter(None == Token.total_supply).all()    
  File "/usr/local/lib/python3.6/site-packages/sqlalchemy/orm/query.py", line 2773, in all    
    return list(self)    
  File "/usr/local/lib/python3.6/site-packages/sqlalchemy/orm/query.py", line 2925, in __iter__    
    return self._execute_and_instances(context)    
  File "/usr/local/lib/python3.6/site-packages/sqlalchemy/orm/query.py", line 2948, in _execute_and_instances    
    result = conn.execute(querycontext.statement, self._params)    
  File "/usr/local/lib/python3.6/site-packages/sqlalchemy/engine/base.py", line 948, in execute    
    return meth(self, multiparams, params)    
  File "/usr/local/lib/python3.6/site-packages/sqlalchemy/sql/elements.py", line 269, in _execute_on_connection    
    return connection._execute_clauseelement(self, multiparams, params)    
  File "/usr/local/lib/python3.6/site-packages/sqlalchemy/engine/base.py", line 1060, in _execute_clauseelement    
    compiled_sql, distilled_params    
  File "/usr/local/lib/python3.6/site-packages/sqlalchemy/engine/base.py", line 1200, in _execute_context    
    context)    
  File "/usr/local/lib/python3.6/site-packages/sqlalchemy/engine/base.py", line 1413, in _handle_dbapi_exception    
    exc_info    
  File "/usr/local/lib/python3.6/site-packages/sqlalchemy/util/compat.py", line 203, in raise_from_cause    
    reraise(type(exception), exception, tb=exc_tb, cause=cause)    
  File "/usr/local/lib/python3.6/site-packages/sqlalchemy/util/compat.py", line 186, in reraise    
    raise value.with_traceback(tb)    
  File "/usr/local/lib/python3.6/site-packages/sqlalchemy/engine/base.py", line 1193, in _execute_context    
    context)    
  File "/usr/local/lib/python3.6/site-packages/sqlalchemy/engine/default.py", line 507, in do_execute    
    cursor.execute(statement, parameters)    
  File "/usr/local/lib/python3.6/site-packages/MySQLdb/cursors.py", line 250, in execute    
    self.errorhandler(self, exc, value)    
  File "/usr/local/lib/python3.6/site-packages/MySQLdb/connections.py", line 50, in defaulterrorhandler    
    raise errorvalue    
  File "/usr/local/lib/python3.6/site-packages/MySQLdb/cursors.py", line 247, in execute    
    res = self._query(query)    
  File "/usr/local/lib/python3.6/site-packages/MySQLdb/cursors.py", line 411, in _query    
    rowcount = self._do_query(q)    
  File "/usr/local/lib/python3.6/site-packages/MySQLdb/cursors.py", line 374, in _do_query    
    db.query(q)    
  File "/usr/local/lib/python3.6/site-packages/MySQLdb/connections.py", line 277, in query    
    _mysql.connection.query(self, query)    
sqlalchemy.exc.OperationalError: (_mysql_exceptions.OperationalError) (2006, 'MySQL server has gone away') [SQL: 'SELECT token.contract_address AS token_contract_address \nFROM token \nWHERE token.total_supply IS NULL'] (Background on this error at: http://sqlalche.me/e/e3q8)    

已使用解决方案

出错后需要 rollback，为了后续程序能运行，给每个涉及 sql 语句的函数用了装饰器

def auto_rollback(func):    
    def wrapper(*args, **kwargs):    
        try:    
            return func(*args, **kwargs)    
        except Exception as err:    
            db.session.rollback()    
            log.error(err)    
            raise err    
    
    return wrapper    

治标不治本系列

把 SQLALCHEMY_POOL_RECYCLE 设成一个较小的数

app.config['SQLALCHEMY_POOL_SIZE'] = 128  # 线程池大小    
app.config['SQLALCHEMY_POOL_TIMEOUT'] = 90  # 超时时间    
app.config['SQLALCHEMY_POOL_RECYCLE'] = 3  # 空闲连接自动回收时间    
app.config['SQLALCHEMY_MAX_OVERFLOW'] = 128  # 控制在连接池达到最大值后可以创建的连接数。    

根据错误日志在需要用数据库的地方先断开连接

db.session.remove()    

失败

将单独的 sql 语句改成 nopool 连接方式

class nullpool_SQLAlchemy(SQLAlchemy):    
    def apply_driver_hacks(self, app, info, options):    
        super(nullpool_SQLAlchemy, self).apply_driver_hacks(app, info, options)    
        from sqlalchemy.pool import NullPool    
        options['poolclass'] = NullPool    
        del options['pool_size']    

解决后又会出现

sqlalchemy.exc.InvalidRequestError: Can't reconnect until invalid transaction is rolled back    

失败

每次访问数据库重新生成 sqlalchemy 连接 ✔

    from xxx import SQLAlchemy    
    from xxx import app    
    db = SQLAlchemy(app)    

最粗暴但是最有效的解决方式，这个问题困扰了将近 3 周，emmm

解决参考

2021年4月19日Python阅读需 3 分钟

转盘点 Python 中的那些冷知识

https://www.cnblogs.com/wongbingming/p/9393186.html

2021年4月19日Python阅读需 1 分钟

要求​

设计思路​

数据库连接工具​

代码​

sql 大概是这个样子​

用sqlalchemy 实现​

执行​

debug​

pycharm The repository located at pypi.douban.com is not a trusted or secure​

配置信息​

报错​

已使用解决方案​

出错后需要 rollback，为了后续程序能运行，给每个涉及 sql 语句的函数用了装饰器​

把 SQLALCHEMY_POOL_RECYCLE 设成一个较小的数​

根据错误日志 在需要用数据库的地方先断开连接​

将单独的 sql 语句改成 nopool 连接方式​

每次访问数据库重新生成 sqlalchemy 连接 ✔​

解决参考​

要求

设计思路

数据库连接工具

代码

sql 大概是这个样子

用sqlalchemy 实现

执行

debug

pycharm The repository located at pypi.douban.com is not a trusted or secure

配置信息

报错

已使用解决方案

出错后需要 rollback，为了后续程序能运行，给每个涉及 sql 语句的函数用了装饰器

把 SQLALCHEMY_POOL_RECYCLE 设成一个较小的数

根据错误日志在需要用数据库的地方先断开连接

将单独的 sql 语句改成 nopool 连接方式

每次访问数据库重新生成 sqlalchemy 连接 ✔

解决参考