postgres数据库入门, python 操作postgres

linux安装postgres参考: https://blog.csdn.net/luanpeng825485697/article/details/80875860

docker 安装postgres参考:https://blog.csdn.net/luanpeng825485697/article/details/81209596

postgres常用linux命令
进入pgsql命令行,切换到Linux用户postgres,然后执行psql:

$ su - postgresLast login: Wed Mar 1 13:16:48 CST 2017 on pts/1$ psqlpsql (9.2.18)Type "help" for help.postgres=#

此时就在数据库postgres中了。

显示用户生成的数据库列表\d显示某个表列属性\d tablename查询数据select camera_ip,camera_id from device;创建数据库CREATE DATABASE mydb;删除数据库DROP DATABASE mydb;创建表CREATE TABLE weather ( city varchar(80), temp_lo int, -- 最低温度 temp_hi int, -- 最高温度 prcp real, -- 湿度 real是一种用于存储单精度浮点数的类型 date date);CREATE TABLE cities ( name varchar(80), location point);创建索引create index tablename_columnname_idx on public.tablename (columnname)删除表DROP TABLE cities;DROP TABLE weather在表中增加行INSERT INTO weather VALUES (San Francisco, 46, 50, 0.25, 1994-11-27);INSERT INTO cities VALUES (San Francisco, (-194.0, 53.0));INSERT INTO weather (city, temp_lo, temp_hi, prcp, date) VALUES (San Francisco, 43, 57, 0.0, 1994-11-29);INSERT INTO weather (date, city, temp_hi, temp_lo) VALUES (1994-11-29, Hayward, 54, 37);

参考:https://blog.csdn.net/taoshujian/article/details/60882172

jsonb相关操作

参考:https://zgljl2012.com/postgresql-cun-qu-jsonb/

建表语句如下:

create table if not exists name_age ( info jsonb)

插入数据
插入数据可以直接以json格式插入:

insert into name_age values({"id":1,"name":"小明", "age":18})

在json里插入新的key值gender,如下:

SELECT info||{"gender":"男"}::jsonb from name_age where (info->>id)::int4 = 1

查询数据

Postgres里的查询需要用到查询符。比如说,我们要查询id为1的数据,语句如下:

select info from name_age where info @> {"id":1}::jsonb

用到了 @> 这个查询符,表明info当前这条记录里的顶层json中有没有id为1的key-value对;有的话则满足条件。

再来一个复杂一点的查询的,查询 age>16 的记录,并且只显示 name ,语句如下:

select info->‘name’ from name_age where (info->>‘age’)::int4 > 16

关于详细运算符使用,请参考官方文档: 9.15. JSON Functions and Operators

 

修改数据
下面,将 age 从 18 改为 22 ,SQL语句:

SELECT info ||{"age":22}::jsonb from name_age where (info->>id)::int4 = 1

上述用法仅适用于9.5以上,9.5以下需要整个记录更新,不可以单独修改某个值。

当然,上述只是修改了查询结果,修改表实际内容的语句:

UPDATE name_age SET info = info ||{"age":22}::jsonb where (info->>id)::int4 = 1

除了操作符以外,还可以使用函数操作: jsonb_set() ,函数签名如下:

jsonb_set(target jsonb, path text[], new_value jsonb[, create_missing boolean])

详细使用可参考 9.15. JSON Functions and Operators

删除数据
删除age这个key,SQL如下:

SELECT info-age from name_age where (info->>id)::int4 = 1

直接用操作符 - 即可。上述同样只是修改了查询结果,如果修改表内容,也需要使用update.

python操作postgres
安装需要的包 pip install psycopg2

新版本的安装方法升级为pip install psycopg2-binary

主要的api接口

1 psycopg2.connect(database="testdb", user="postgres", password="cohondob", host="127.0.0.1", port="5432") 这个API打开一个连接到PostgreSQL数据库。如果成功打开数据库时,它返回一个连接对象。 2 connection.cursor()该程序创建一个光标将用于整个数据库使用Python编程。 3 cursor.execute(sql [, optional parameters])此例程执行SQL语句。可被参数化的SQL语句(即占位符,而不是SQL文字)。 psycopg2的模块支持占位符用%s标志 例如:cursor.execute("insert into people values (%s, %s)", (who, age))4 curosr.executemany(sql, seq_of_parameters)该程序执行SQL命令对所有参数序列或序列中的sql映射。 5 curosr.callproc(procname[, parameters])这个程序执行的存储数据库程序给定的名称。该程序预计为每一个参数,参数的顺序必须包含一个条目。6 cursor.rowcount这个只读属性,它返回数据库中的行的总数已修改,插入或删除最后 execute*().7 connection.commit()此方法提交当前事务。如果不调用这个方法,无论做了什么修改,自从上次调用commit()是不可见的,从其他的数据库连接。8 connection.rollback()此方法会回滚任何更改数据库自上次调用commit()方法。9 connection.close()此方法关闭数据库连接。请注意,这并不自动调用commit()。如果你只是关闭数据库连接而不调用commit()方法首先,那么所有更改将会丢失! 10 cursor.fetchone()这种方法提取的查询结果集的下一行,返回一个序列,或者无当没有更多的数据是可用的。11 cursor.fetchmany([size=cursor.arraysize])这个例程中取出下一个组的查询结果的行数,返回一个列表。当没有找到记录,返回空列表。该方法试图获取尽可能多的行所显示的大小参数。12 cursor.fetchall()这个例程获取所有查询结果(剩余)行,返回一个列表。空行时则返回空列表。

简单的demo

# -*- coding: utf-8 -*-import psycopg2import loggingclass PyPostgres: def __init__(self): self.conn = None self.cursor = None # 链接postgres sql数据库 def connect(self, host, user, passwd, db, port=5432, charset="utf8"): try: self.conn = psycopg2.connect( "host=%s port=%s dbname=%s user=%s password=%s" % (host, port, db, user, passwd)) self.cursor = self.conn.cursor() logging.info(connect postgres success) except Exception as e: logging.error(connect postgres database %s error! %s % (db, e)) return False return True # 指定sql命令查询 def query(self, sqlcommand, args=None): try: self.cursor = self.conn.cursor() self.cursor.execute(sqlcommand, args) result = self.cursor.fetchall() except Exception as e: logging.error("postgres query error: %s\n mysql:%s args: %s" % (e, sqlcommand, args)) return False return result # 指定sql命令执行 def execute(self, sqlcommand, args=None): try: self.cursor = self.conn.cursor() if isinstance(args, (list, tuple)) and len(args) > 0 and isinstance(args[0], (list, tuple)): line = self.cursor.executemany(sqlcommand, args) else: line = self.cursor.execute(sqlcommand, args) except Exception as e: # traceback.print_exc() logging.error("postgres execute error: %s" % e) return False return line, self.cursor # 提交 def commit(self): self.conn.commit() # 回滚 def rollback(self): self.conn.rollback() # 关闭链接 def close(self): if self.cursor: self.cursor.close() if self.conn: self.conn.close() logging.info(close postgres success) # 插入数据 def insert_data(self, table_name, data_dict): data_values = "(" + "%s," * (len(data_dict)) + ")" data_values = data_values.replace(,), )) db_field = data_dict.keys() data_tuple = tuple(data_dict.values()) db_field = str(tuple(db_field)).replace("", ‘‘) sql = """ insert into %s %s values %s """ % (table_name, db_field, data_values) params = data_tuple self.execute(sql, params) self.commit() # self.close()# docker run --name postgres1 -e POSTGRES_PASSWORD=admin -p 5432:5432 -d postgres:latest# su - postgres# psql# CREATE DATABASE IF NOT EXISTS testdb default charset utf8 COLLATE utf8_general_ci;if __name__ == "__main__": py_sql = PyPostgres() py_sql.connect(host=127.0.0.1, user=postgres, passwd=adminroot, db=test, port=5432) sql = drop table if exists public.member # 不能使用user , 因为是postgres关键词 py_sql.execute(sql) py_sql.commit() create_table = create table public.member(id integer not null primary key ,username varchar(64) not null,password varchar(64) not null, phone varchar(64)); py_sql.execute(create_table) py_sql.commit() insert_data = "insert into public.member(id,username,password,phone) values(1,‘luanpeng‘,‘123456‘,‘112121212‘);" py_sql.execute(insert_data) py_sql.commit() update_data = "update public.member set username=‘luanpeng1‘ where id=1" py_sql.execute(update_data) py_sql.commit() rows = py_sql.query(select * from public.member) if rows: for row in rows: print(row) print(id=, row[0], ,username=, row[1], ,password=, row[2], ,phone=, row[3], \n) delete_data = "delete from public.member where id=1" py_sql.execute(delete_data) py_sql.commit() truncate_data = "truncate table public.member" py_sql.execute(truncate_data) py_sql.commit()

 

相关文章