这篇文章主要介绍了基于postgreSql 常用查询小结,具有很好的参考价值,希望对大家有所帮助。一起跟随小编过来看看吧。
1. 日期格式转化(参考)
1select beg_time, end_time, extract(epoch from to_timestamp(end_time,'yyyy-mm-dd-HH24-MI-SS-US'))-extract(epoch from to_timestamp(beg_time,'yyyy-mm-dd-HH24-MI-SS-US')) from cdb_all_iu_data where beg_time > '2017-09-21'
注:beg_time, end_time以TEXT形式存储,求时间差时转化为时间戳再相减得到结果(s)
2. select * from (中间结果) t
select count(*) from (
select chkid, count(*) from abc_table GROUP BY chkid) t
补充:自己写的postgreSQL查询语句
我就废话不多说了,大家还是直接看代码吧~
import psycopg2
class PostgreConn():
'''
数据库连接类
'''
def __init__(self, database, user, password, host, port):
self.conn = psycopg2.connect(database=database, user=user, password=password, host=host, port=port)
print('数据库连接成功')
self.cur = self.conn.cursor()
self.rows = None
def cur(self):
return self.cur()
def execute(self, sql, fetchone=0):
self.cur.execute(sql)
if fetchone:
self.rows = self.cur.fetchone()
else:
self.rows = self.cur.fetchall()
return self.rows
def close(self):
self.cur.close()
self.conn.close()
print('数据库连接关闭')
def select_sql(table, keys, conditions, isdistinct=0):
'''
生成select的sql语句
@table,查询记录的表名
@key,需要查询的字段
@conditions,插入的数据,字典
@isdistinct,查询的数据是否不重复
'''
if isdistinct:
sql = 'SELECT distinct %s ' % ",".join(keys)
else:
sql = 'SELECT %s ' % ",".join(keys)
sql += ' from %s ' % table
if conditions:
sql += ' WHERE %s ' % dict_str_and(conditions)
return sql
def dict_str_and(dictin):
'''
将字典变成,key='value' and key='value'的形式
'''
tmplist = []
for k, v in dictin.items():
tmp = "%s='%s'" % (str(k), str(v))
tmplist.append(' ' + tmp + ' ')
return ' and '.join(tmplist)
def fSqlResult(r,key_list):
'''
:param r: 数据库fetchall的结果
:param key_list: 查询字段的keys
:return:
format SQL Result 格式化数据库查询的结果,转化成包含多个字典的列表格式,即((1,2),(3,4))->[{"key1":1,"key2":2},{"key1":3,"key2":4}]
返回 @dict 查询结果
'''
mlist=[]
l=len(key_list)
if r:
for item in r:
tmp={}
for i in range(l):
tmp[key_list[i]]=str(item[i])
mlist.append(tmp)
return mlist
conn = PostgreConn('settle', 'admin', 'settle8', '123.57.285.89', '5432')
key_list = ['user_id']
sql = select_sql('st_user', key_list, {'phone': '138****'})
print(sql)
r = conn.execute(sql)
re = fSqlResult(r, key_list)
print(re)
conn.close()
文章来源:脚本之家
来源地址:https://www.jb51.net/article/204847.htm
申请创业报道,分享创业好点子。点击此处,共同探讨创业新机遇!