當(dāng)前位置:首頁(yè) >  站長(zhǎng) >  數(shù)據(jù)庫(kù) >  正文

基于postgreSql 常用查詢小結(jié)

 2021-05-26 16:52  來(lái)源: 腳本之家   我來(lái)投稿 撤稿糾錯(cuò)

  域名預(yù)訂/競(jìng)價(jià),好“米”不錯(cuò)過(guò)

這篇文章主要介紹了基于postgreSql 常用查詢小結(jié),具有很好的參考價(jià)值,希望對(duì)大家有所幫助。一起跟隨小編過(guò)來(lái)看看吧。

1. 日期格式轉(zhuǎn)化(參考)

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形式存儲(chǔ),求時(shí)間差時(shí)轉(zhuǎn)化為時(shí)間戳再相減得到結(jié)果(s)

2. select * from (中間結(jié)果) t

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

 

補(bǔ)充:自己寫(xiě)的postgreSQL查詢語(yǔ)句

我就廢話不多說(shuō)了,大家還是直接看代碼吧~

import psycopg2
class PostgreConn():
  '''
  數(shù)據(jù)庫(kù)連接類
  '''
  def __init__(self, database, user, password, host, port):
    self.conn = psycopg2.connect(database=database, user=user, password=password, host=host, port=port)
    print('數(shù)據(jù)庫(kù)連接成功')
    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('數(shù)據(jù)庫(kù)連接關(guān)閉')
 
def select_sql(table, keys, conditions, isdistinct=0):
  '''
    生成select的sql語(yǔ)句
  @table,查詢記錄的表名
  @key,需要查詢的字段
  @conditions,插入的數(shù)據(jù),字典
  @isdistinct,查詢的數(shù)據(jù)是否不重復(fù)
  '''
  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: 數(shù)據(jù)庫(kù)fetchall的結(jié)果
  :param key_list: 查詢字段的keys
  :return:
  format SQL Result 格式化數(shù)據(jù)庫(kù)查詢的結(jié)果,轉(zhuǎn)化成包含多個(gè)字典的列表格式,即((1,2),(3,4))->[{"key1":1,"key2":2},{"key1":3,"key2":4}]
  返回 @dict 查詢結(jié)果
  '''
  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()

文章來(lái)源:腳本之家

來(lái)源地址:https://www.jb51.net/article/204847.htm

申請(qǐng)創(chuàng)業(yè)報(bào)道,分享創(chuàng)業(yè)好點(diǎn)子。點(diǎn)擊此處,共同探討創(chuàng)業(yè)新機(jī)遇!

相關(guān)文章

熱門(mén)排行

信息推薦