要求
一億數(shù)據(jù) 108
測試用5w數(shù)據(jù)
預(yù)測時間為 結(jié)果時間 2000
設(shè)計思路
- 程序執(zhí)行20遍求平均值
- 結(jié)束時間 - 開始時間
- 不同python引擎
- 不同數(shù)據(jù)量 然后commit提交 響應(yīng)速度
數(shù)據(jù)庫連接工具
- [x] MySQL-Python
- [x] 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("數(shù)據(jù)庫關(guān)閉")
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}函數(shù)寫入耗時{sec}秒".format(func=fn.__name__, sec=seconds))
# print(u"{func}函數(shù)每{count}條數(shù)數(shù)據(jù)寫入耗時{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框架 誰快 包括數(shù)據(jù)組裝"""
init("mysql+pymysql://root:root@192.168.14.147:3306/efficiency_test")
print("orm框架插入數(shù)據(jù)")
# iinsert_many函數(shù)每500條數(shù)數(shù)據(jù)寫入耗時19.671629905700684秒
insert_many()
print("sql語句插入數(shù)據(jù)")
# insert_many_by_sql函數(shù)每500條數(shù)數(shù)據(jù)寫入耗時17.977628707885742秒
insert_many_by_sql()
pass
def main():
print('測試開始')
# insert_many函數(shù)寫入耗時168.07286262512207秒
init("mysql+mysqlconnector://root:root@192.168.14.147:3306/efficiency_test")
insert_many()
# insert_many函數(shù)寫入耗時64.85304117202759秒
init("mysql://root:root@192.168.14.147:3306/efficiency_test") # 默認(rèn)使用MySQLdb
insert_many()
# insert_many函數(shù)寫入耗時64.692676067352295秒
init("mysql+pymysql://root:root@192.168.14.147:3306/efficiency_test")
insert_many()
# insert_many函數(shù)寫入耗時66.991496086120605秒
init("mysql+mysqldb://root:root@192.168.14.147:3306/efficiency_test")
insert_many()
if __name__ == '__main__':
main()
main2()
