基于postgreSql 常用查询小结

select 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'

select count (*) from (

select chkid, count (*) from abc_table GROUP BY chkid) t

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 ()