本文共 2921 字,大约阅读时间需要 9 分钟。
# 整表查询,返回所有数据 def mysql_query_table(self, table): db, cursor = self.mysql_creat_connect() sql = "SELECT * FROM {} ".format(table) cursor.execute(sql) res = [] for x in cursor.fetchall(): res.append(x) self.mysql_close_connect(db, cursor) # logging.info(res) return res # 输入sql语句查询,按类型返回数据 def mysql_query_sql(self, sql, date=[], resmodel='all', many=2): db, cursor = self.mysql_creat_connect() cursor.execute(sql, date) if resmodel == 'all': res = cursor.fetchall() elif resmodel == 'one': res = cursor.fetchone() else: res = cursor.fetchmany(many) self.mysql_close_connect(db, cursor) return res # 输入输入指定时间段查询 def mysql_query_time(self, table, starttime, endtime, field): db, cursor = self.mysql_creat_connect() sql = """SELECT * FROM {0} WHERE {3} BETWEEN '{1}' AND '{2}' """.format(table, starttime, endtime, field) cursor.execute(sql) res = [] for x in cursor.fetchall(): res.append(x) self.mysql_close_connect(db, cursor) # logging.info(res) return res # 查询某天的数据 def mysql_query_time_day(self, table, day, field): db, cursor = self.mysql_creat_connect() sql = """SELECT * FROM {0} WHERE {1} BETWEEN '{2} 00:00:00' AND '{2} 23:59:59' """.format(table, field, day) cursor.execute(sql) res = [] for x in cursor.fetchall(): res.append(x) self.mysql_close_connect(db, cursor) # logging.info(res) return res
简单的封装一下:
# 查询某天的数据def query_time_day(self, table, day, field='DTIME'): res = self.mysql_query_time_day(table, day, field) return res
查询一下
x = mysqlexample()y = x.query_time_day('atest_doubledate', '2018-12-5')for i in y: pass logging.info(i)
INFO:root:(16, datetime.datetime(2018, 12, 5, 13, 41, 58), 82.4066)INFO:root:(17, datetime.datetime(2018, 12, 5, 13, 59, 33), 595.532)INFO:root:(18, datetime.datetime(2018, 12, 5, 14, 0, 4), 913.284)INFO:root:(19, datetime.datetime(2018, 12, 5, 14, 18, 47), 968.804)INFO:root:(20, datetime.datetime(2018, 12, 5, 14, 22), 987.586)INFO:root:(21, datetime.datetime(2018, 12, 5, 16, 27, 25), 243.352)INFO:root:(40, datetime.datetime(2018, 12, 5, 17, 19, 4), 210.541)INFO:root:(37, datetime.datetime(2018, 12, 5, 17, 15, 21), 26.8079)
ok,完全没有问题。
···················································································································································································
#查看某天的数据sql = SELECT * FROM table WHERE ( datediff ( DTIME , day ) = 0 )# 查看进7天的数据,查看最近多少天都可以按照这个更改,改一下日期就好了。sql = "SELECT * FROM atest_doubledate WHERE DATE_SUB(CURDATE(), INTERVAL 7 DAY) <=DTIME "
查询时 碰到了一个小坑,也可能是我没有注意, table 是不能用引号引起来的,如果引起来会报错1064,
转载地址:http://latzb.baihongyu.com/