postgres

首页postgres
20
Jan
0

关于postgres连续周分组统计的解决方法

最近需要做一个每周趋势图
因为在一条SQL里需要对每个分组过滤开始和结束时间
所以略显麻烦,要花较多时间去弄,所以把思路先暂时放在这里.

  1. 首先拆分得到各周分别是当年的第几周
  2. (week from now())

2.由于我们是需要一序列,所以这里要用到一个函数generate_series
作用是产生一系列的连续填充数据 generate_series(start,stop, step interval)
第三个参数可以是天,周,年,或者bigint等数据类型,这里我们需要的是周'1 weeks'
另也可以直接加7天timestamp '2018-01-21'::timestamp + '1 day'

--这样我们能得到年和周的记录
select 
    extract (year from date_series.date) as year ,
    extract (week from date_series.date) as week,date_series.date
from (
      select generate_series(
          timestamp '2017-10-01', 
          timestamp '2018-01-31',  '1 weeks') as date
) date_series
  1. 上面用来进行分组展示,接下来是对每一周的起始时间的计算

    --得到一个日期到底是周几 0代表周日 取值范围:0-6
    select extract (dow from now())

  2. 根据周几再减或加几天得到一周的开始

    select timestamp '2018-01-21'::timestamp + '1 day'

  3. 其它就是过滤等等不提了 结束
26
Jul
0

postgres的json处理 上

只整理了几个常用用法 jsonb也未尝试

--判断表是否存在
select count(*) from pg_class where relname = 'familis_j'
--删除表
drop table IF EXISTS familis_j
--建表
 create table IF NOT EXISTS familis_j(id serial primary key,profile json)
--清除所有记录
truncate table familis_j
--插入数据
insert into familis_j(profile)values('
 {
    "root": {
        "root": "100mb", 
        "home": "200mb", 
        "data": "600mb"
    }, 
    "total": "1000mb", 
    "data": [
        "this is one", 
        "this is two", 
        "this is three"
    ], 
    "iteams": [
        {
            "item": {
                "key1": "value1", 
                "key2": "value2"
            }
        }, 
        {
            "item": {
                "key3": "value3", 
                "key4": "value4"
            }
        }
    ]
}
 ')

--取单层KEY值
select json_extract_path(profile,'root') from familis_j
select json_extract_path_text(profile,'root') from familis_j
--此两行区别 ->>(json_extract_path)返回是json ->()返回文本
select profile ->>'root' as rootValue from familis_j
select profile ->'root'as rootValue from familis_j
--取多层KEY值
select json_extract_path(json_extract_path(profile,'root'),'data') from familis_j

--取数组值
select json_array_elements(json_extract_path(profile,'data')) as json_arr from familis_j

--取item子明细  #>> json  #> 文本
select json_array_elements(json_extract_path(profile,'iteams')) #>> '{item}' from familis_j
select json_array_elements(json_extract_path(profile,'iteams')) #> '{item}' from familis_j

--取节点数量
select json_array_length(json_extract_path(profile,'iteams')) from familis_j

--取items
select profile #> '{iteams}'  from familis_j      --json
select profile #>> '{iteams}'  from familis_j     --文本
select profile ->> 'iteams'  from familis_j       --文本

--取下标为0的item整组数据
select profile #> '{iteams}' -> 0  from familis_j 
--取下标为0的item内的数据
select profile #> '{iteams}' -> 0 #>> '{item}' from familis_j 
--取下标为0的item内key1的数据
select profile #> '{iteams}' -> 0 #> '{item,key1}' from familis_j     --json
select profile #> '{iteams}' -> 0 #>> '{item,key1}' from familis_j    --文本 
12
Jul
0

sqlalchemy,psycopg2操作postgres的ORM

1.定义连接器及子类

from Common.Config import configer
import sqlalchemy as sqla
import sqlalchemy.orm as sqlorm
from sqlalchemy.ext.declarative import declarative_base as sqla_declarative_base

class PostgresConnection(object):
    session = None
    base = None
    def __init__(self):
        self.base = sqla_declarative_base()
        #engine = sqla.create_engine ('postgresql://postgres:postgres@10.200.163.233:5432/myTest', echo=True,pool_size=10)
        engineStr = ('postgresql://' +
                                      configer.Database['username'] +
                                      ':' +
                                      configer.Database['password'] +
                                      '@' +
                                      configer.Database['serverip'] +
                                      ':' +
                                      configer.Database['port'] +
                                      '/' +
                                      configer.Database['dbname'])
        self.engine = sqla.create_engine(engineStr,
                                     echo=bool(configer.Database['isecho']),
                                     pool_size=int(configer.Database['poolcount']))
        self.base.metadata.bind = self.engine
        self.session = sqlorm.scoped_session(sqlorm.sessionmaker(bind=self.engine))
connection = PostgresConnection()

2.类定义 外键参数定义

from Common.Connection import connection
import sqlalchemy as db
from sqlalchemy import ForeignKey
from sqlalchemy.orm import relationship, backref

class Orders(connection.base):
    __tablename__ = 'orders'
    id = db.Column('id', db.Integer, primary_key=True, autoincrement=True)
    orderid = db.Column('orderid',db.String(20),unique=True,nullable=False)
    name = db.Column('name',db.String(20))

class Details(connection.base):
    __tablename__ = 'details'
    id = db.Column('id', db.Integer, primary_key=True, autoincrement=True)
    detailname = db.Column('detailname',db.String(20))
    #CASCADE 级联更新,DELETE 删除  RESTRICT 不允许对主表纪录操作  外键操作
    orderid = db.Column(db.String(20), ForeignKey('orders.orderid',onupdate='CASCADE',ondelete='CASCADE'))
    orders = relationship("Orders", backref="fkOrder")

创建表 当表存在不操作 表结构进行修改后也不会进行操作

connection.base.metadata.create_all()

可以用插件sqlalchemy-migrate协助处理

3.增

   import DataModel.Test

    session = self.connection.session()
    try:
        re = DataModel.Test.Orders(orderid = '20170607_001',name = 'wang wu')
        session.add(re)
        session.flush()
        re = DataModel.Test.Details(orderid = '20170607_001',detailname = 'wang wu detailname')
        session.add(re)
        session.flush()
        re = DataModel.Test.Details(orderid = '20170607_001',detailname = 'wang wu detailname')
        session.add(re)
        session.flush()
        session.commit()
    except Exception as e:
        session.rollback()

4.删

    

from sqlalchemy import text
        re = session.query(DataModel.Job.Receives).filter(text("id =2"))
        session.delete(re[0])

5.改

    

from sqlalchemy import text
        re = session.query(DataModel.Job.Receives).filter(text("id =3"))
        re[0].data = '{"name":"zhangsan1"}'
        sql = str(session.add(re[0]))
        session.commit()

6.查

import DataModel.Job
session = self.connection.session()
try:
    # res = session.query(DataModel.Job.Receives).order_by(DataModel.Job.Receives.id)
    # res = session.query(DataModel.Job.Receives.id,DataModel.Job.Receives.jobid).all()
    # res = session.query(DataModel.Job.Receives).all()[2:4]
    # res = session.query(DataModel.Job.Receives).filter(DataModel.Job.Receives.id == 1)
    # res = session.query(DataModel.Job.Receives).filter(DataModel.Job.Receives.id != 1)
    # res = session.query(DataModel.Job.Receives).filter(DataModel.Job.Receives.jobid.like('%0%'),DataModel.Job.Receives.id.in_([1,3]))
    # res = session.query(DataModel.Job.Receives).filter(DataModel.Job.Receives.jobid.like('%0%'),
    #                                                    DataModel.Job.Receives.id > 1)

    from sqlalchemy import and_,or_
    # res = session.query(DataModel.Job.Receives).filter(and_(DataModel.Job.Receives.jobid.like('%0%'),
    #                                                    DataModel.Job.Receives.id == 2))
    # res = session.query(DataModel.Job.Receives).filter(or_(DataModel.Job.Receives.id == 1,
    #                                                         DataModel.Job.Receives.id == 2))
    ########################
    # res = session.query(DataModel.Job.Receives).filter(or_(DataModel.Job.Receives.id == 1,
    #                                                        DataModel.Job.Receives.id == 2)).first()
    # result = result + '\n\r' + str(res.id) + res.data + res.jobid
    ########################
    from sqlalchemy import text
    # res = session.query(DataModel.Job.Receives).filter(text("id > 0 or id =1"))
    # res = session.query(DataModel.Job.Receives).from_statement(text("select * from receives where id > 1"))
    ################
    # res = session.query(DataModel.Job.Receives).filter(text("id > 0 or id =1")).count()
    # result = str(res)           # 使用from_statement 则不能统计
    #################
    # res = session.query(DataModel.Job.Receives).filter(text("id > 0 or id =1")).limit(2)
    res = session.query(DataModel.Job.Receives).filter(text("id > 0 or id =1"))[2:3]

    for m in res:
        result = result + '\n\r' + str(m.id) + m.data + m.jobid

7.修改单条记录时
按KEY值修改 非全字段对比 这样会造成多个地方同时修改 要想精确控制全字段对比 需要手写SQL方法

  1. 外键的实际应用中写法 ,可以多级外键 swagger.Tags = [tag,tag]

    class Swagger(swaggerConnection.Connection.base):

    __tablename__ = 'swagger'
    id = db.Column('id',db.Integer,primary_key=True,autoincrement=True)
    url = db.Column('url',db.String(200),unique=False,nullable=True)
    name = db.Column('name',db.String(200),unique=False,nullable=True)
    host = db.Column('host',db.String(200),unique=False,nullable=True)
    basePath = db.Column('basepath',db.String(200),unique=False,nullable=True)
    Tags = relationship("Tags", order_by="Tags.id", backref="swagger")
    

    class Tags(swaggerConnection.Connection.base):

    __tablename__ = 'tags'
    id = db.Column('id',db.Integer,primary_key=True,autoincrement=True)
    swaggerid = Column(Integer, ForeignKey('swagger.id',onupdate='CASCADE',ondelete='CASCADE'))
    name = db.Column('name',db.String(100),unique=False,nullable=True)
    description = db.Column('description',db.String(200),unique=False,nullable=True)
    Paths = relationship("Paths", order_by="Paths.id", backref="tags")
    
06
Jul
0

postgres9.6安装到centos7.2

官方安装步骤:
1.yum install https://download.postgresql.org/pub/repos/yum/9.6/redhat/rhel-7-x86_64/pgdg-centos96-9.6-3.noarch.rpm
2.yum install postgresql96
3.yum install postgresql96-server
4./usr/pgsql-9.6/bin/postgresql96-setup initdb
5.systemctl enable postgresql-9.6
6.systemctl start postgresql-9.6

CENTOS6步骤456为
4.service postgresql-9.6 initdb
5.chkconfig postgresql-9.6 on
6.service postgresql-9.6 start

更改默认密码:
sudo -u postgres psql
alter user postgres password 'postgres';
设置:
1./var/lib/pgsql/9.6/data/postgresql.conf
打开listen_addresses和port设置
2./var/lib/pgsql/9.6/data/pg_hba.conf
IPV4下增加
host all all 10.200.161.15/16 md5
(10.200.161.15/16 代表15-16)

查看postgres运行状态
netstat -lanp|grep 5432
ps -ef | grep postgres

可以访问了!

错误1:
FATAL:no pg_hba.conf entry for host '192.1.x.x',user 'postgres',database 'postgres',SSL off
解决:
设置的第二步错了
错误2:
直接报连接不上
解决:
1.服务器防火墙
2.设置第一步没有设置
错误3:
在CENTOS本机连接时(其它电脑连接时不报)报,Ident authentication failed for user "postgres"
解决:
a.postgresql.conf listen_addresses = '*'
b.修改pg_hba.conf文件

 # IPv6 local connections:
 host    all             all             ::1/128                 ident
 把ident修改为trust
 host all all 0.0.0.0/0 trust