psycopg postgresql 第一次整理

psycopg postgresql 第一次整理

Psycopg2用法及实现

导包 import psycopg2

获取连接：conn = t（dsn）

dsn 为一个字符串，k-v形式用空格连起来 ：dbname=xx user=xx port=xx password=xx ..

获取游标对象来操作数据库：cur = conn.cursor()

操作数据库：cur .execute("sql语句") 如果是创建、插入、更新则直接commit，如果是select语句则使用fetchxx来获取

操作数据库&#ute("select * from table")

获取查询结果单一：single_back = cur.fetchone()

获取查询结果list：list_back = cur.fetchall()

提交或者回滚来结束事务

提交：connmit()

回滚&#llback()

占位

psycopg中只有%s，无论是数字还是字符串都是%s。

ute("delete from grade_table where name = '%s' " % (name)) 语法可行，但是会造成sql注入

sql = "delete from grade_table where name = %s" % ("'ff' ;delete from grade_table;") SQL INJECTION

sql = "delete from grade_table where name = '%s'" % ("ff' or 'a'='a") SQL INJECTION

ute（）只接受序列：元祖和字典

元祖形式&#ute（"""insert into table （name，age）values（%s，%s）"""，（“名字”，12））如果是单个占位，则（value，） 只接收序列

字典形式（命名参数）&#ute（"""insert into table （name，age）values（%(name)s，%(age)s）"""，{'name':name,'age':12}）

*动态生成sql查询（文档举例） format

ute("INSERT INTO %s VALUES (%s)", ('numbers', 10)) WRONG

ute(SQL("INSERT INTO {} VALUES (%s)").format(Identifier('numbers')),(10,)) Correct

查询参数注意点

字符串有双引号隔开，则单引号可以不使用，占位符也不要使用%相连

SQL = "INSERT INTO authors (name) VALUES (%s);" Note: no quotes. %s这里不要使用引号，psycopg2会自动加

data = ("O'Reilly", )

ute(SQL, data) Note: no % operator 不要使用%相连，和python语法不一样

python中和sql中数据类型的适用

python：-None-True-False

sql：------NUll-- true-false

python中整数类型int--浮点数类型float--用户定义（标度，精度）Decimal > 数据库中numerical

编码和unicode处理和二进制适应 todo

字符串：发送：字符串在发送之后，到达后端之前会自动编码cncoding，不能编码会报错。python3接收的是字符串，python2接收的是unicode串

unicode：psycopg可以和PostgreSQL交换Unicode数据。接收的时候python3自动解码，python2是数据库客户端编码的8位二进制数据字符串对象，必须注册类型转换器才能接受unicode对象

二进制：python2中，如果str是二进制数据，使用psycopg2.Binary（二进制数据）来传递给字段

mypic = open('picture.png', 'rb').read()
ute("insert into blobs (file) values (%s)",
    (psycopg2.Binary(mypic),))

列表适应：python列表被转换成postgresql 数组。

psycopg2中ANY运算符功能等同于python中的in运算符，可以使用空列表

元祖适应：不能使用空元祖，

事务：同一个连接创建的所有游标发出的所有命令在同一个事务中进行，一个命令失败，都不执行，必须rollback（）然后才可以进一步操作；

with块使用：

t(DSN) as conn:  
    with conn.cursor() as curs:
        ute(SQL)

conn = t(DSN)
with conn:
    with conn.cursor() as curs: 
        ute(SQL1)
with conn:
    with conn.cursor() as curs:
        ute(SQL2)
conn.close()

常见问题

1. 事务在开启之后，直到commit（）或者rollback（）才结束。避免在前一个事务中启动动新事务，确保已经commit（）或者rollback（）

2. 事务中开启新事务（事务嵌套），savepoint

3. 如果前面的事务中有语句失败，则必须rollback（）才能开始新事务

4. 占位符必须是%s，无论是字符串还是数字还是其他类型的

5. 位置参数必须作为序列传递，单个元素的元祖，需要逗号

关于PostgreSQL的安装及使用

遇到的问题：

无法登陆：服务器是否启动，用户是否存在，数据库是否存在

无法启动：默认端口5432，启动下一个服务必须指定端口

用户和库存储到一个相同的目录：创建用户和数据库的时候需要指定端口，否则都存储到了同一个默认目录

安装：brew install postgresql

卸载：brew uninstall postgresql

启动：pg_ctl -D /usr/local/var/postgres -l /usr/local/var/postgres/server.log start

jianhua$ pg_ctl start -D /Users/jianhua/Desktop/005/data/pg -o "-p 35331" -l /Users/jianhua/Desktop/005/data/pg/postgresql.log

停止：pg_ctl stop -D /Users/jianhua/Desktop/005/data/pg -o "-p 35331" -m fast

mac中：PostgreSQL初始化：initdb -D 路径

启动服务：pg_ctl start -D 路径 -o "-p port_number" -l 路径/postgresql.log

停止服务：pg_ctl stop -D 路径 -o "-p port_number" -m fast

创建用户指定端口：createuser -s user_name -h localhost -p port_number -P

创建数据库指定端口和归属：createdb dbname -O user_name -p port_number

登陆：psql -U user_name -d dbname -p port_number

---

初始化：initdb -D 路径/data/pg_datacreateuser username -Pcreatedb dbname -O username -E UTF8 -epsql -h xxxxx -U dbuser -p port -d dbname

Postgresql 对象关系型数据库管理系统[]:可选 {}和|:必须选取一个候选 …:前面元素可重复 sql语句以 ；结尾注意单双引号的使用客户端和服务器模式，支持并发连接，所以，主服务器一直在等待连接，每一个postgres程序就是一个链接

某一个端口的用户：

createuser -s jh01 -h localhost -p 35331 -P

createdb jh01 -O jh01 -p 35331

psql -U jh01 -d jh01 -p 35331

创建数据库用户：create user username with password mima

创建数据库指定拥有者：create database dbname ower username

为拥有者添加权限：grant all privileges on database dbname to username

第二种方法：postgresql控制台

sudo add user Jianhua 新建Linux用户

sudo su - postgres 切换到postgres的用户

psql 登陆控制台

password postgres 为postgres用户设置密码

create user jianhua with password ‘密码’ 为刚才创建的Linux用户设置密码

create database jianhua01 owner jianhua 创建用户jianhua的数据库

grant all privileges on database jianhua01 to jianhua 为用户添加数据库权限

q 推出控制台 （ctrl+D 也行）

登陆数据库：psql命令psql -U Jianhua -d jianhua01 -h 127.0.0.1 -p 5432

列举数据库：l -show databases

列举数据表：dt -show tables

查看表结构：d tbname -desc tbname

切换数据库：c dbname

创建数据库：createdb dbname -O username -E UTF8 -e

创建数据表：create table address_list（字段1 约束，字段2 约束。。。）；

删除数据库：drop database jianhua01 慎用———

删除用户：drop user jianhua 慎用———

postgresql数据库中on conflict ...用法和with 语句：

on conflict ... do nothing 可执行则执行，否则什么也不干

insert into 。。。on conflict 。。。 do update 。。。如果能插入就插入出错就更新

WITH cnt AS (SELECT did, count(*) FROM question WHERE status=4 GROUP BY did) >

解释：(SELECT did, count(*) FROM question WHERE status=4 GROUP BY did)的结果集当作临时表cnt ，给后面查询使用

知识点补充：

设置环境变量：

os.environ['环境变量名称']='环境变量值' #其中key和value均为string类型

os.putenv('环境变量名称', '环境变量值')

获取环境变量：

os.environ['环境变量名称']

os.getenv('环境变量名称')

命令行运行Python脚本传入参数的常用方式：

1. sys.argv[]

   sys.argv 获取到的是一个列表

   (py3) Cheftins-MBP:bin jianhua$ python jh_config.py "good" "good" "hh"
   ​
   输入参数为['jh_config.py', 'good', 'good', 'hh']  第一个参数索引为0，值是文件名

2. argparse包解析

   import argparse 导包

   parse = argparse.ArgumentParser() 创建一个解析处理器parser.add_argument('-shortname','--fullname',type=?,default=?)parser.add_argument('-f', action='store_false', default=True, help="帮助信息")

   可以添加多个参数

   parser.add_argument('para')

   这种格式不满足上面的通用格式，但是可用，并且最简单，表示para这个参数必须要有，存在简称和全称的，在输入时可有可无

   args = parser.parse_args() 获取参数para = args.'fullname'

log日志：

logger&#Logger（“name”）获取日志实例
formatter= logging.Formatter() 日志格式化输出
logger.setLevel(logging.INFO) 日志输出级别

git

git add -p 查看具体的代码，

Stage this hunk [y,n,q,a,d,/,K,j,J,g,e,?]? y

y 表示同意提交，n表示不同意提交，如果出现一半提交，一般半不提交那么就用s打断，分块来判断

git diff --cached 在commit 之前看，整体对比。

git push --set-upstream origin prefetch_pdf 把本地分支提交到远程服务器

git stash 零时存储未保存的修改，可以切换到其他分支

git stash pop 回到修改状态，继续开发

ute(SQL("delete from {} where name = %s").format(Identifier('grade_table')), (name,)) SQL是sql模块中的一个类，提供join()和format()方法来生成查询语句。 Identifier也是sql模块中的一个类，一般表示数据库对象名称，表名或者字段名

为什么在命令行里不需要 commit ？ echo :AUTOCOMMIT 显示是否自动提交，postgres=# set AUTOCOMMIT on/off dbh.set_session(readonly=True , autocommit = True);

postgresql支持俩中json数据类型，一种是json，一种jsonb

json：完全拷贝，使用的时候解析，所以存储块，使用慢。 插入：insert into json_data (jsondata) values('{"name" : "l isi"}');

查询：select * from json_data ; id | jsondata ----+---------------------- 1 | {"name":"lisi"} 2 | {"name" : "l isi”} (2 rows)

Jsonb：存储的时候解析，去掉不需要的空格等，所以存储慢，使用快。 插入：insert into jsonb_data (jsondata) values('{"name" : "l isi"}'); 查询：select * from jsonb_data ; id | jsondata ----+------------------- 1 | {"name": "lisi"} 2 | {"name": "l isi"} (2 rows)

[({'name': 'lisi'},), ({'name': 'l isi'},)] fetchall返回一个由元祖组成的列表，fetchone 返回一个元祖 ({'name': 'lisi’},)。进一步处理取字典

任务：数据库连表查询

1. select * from grade,students; 造成笛卡尔积查询 m*n

2. select * from students as s inner join grade as g ade = g.grade; 内连接查询,

3. select * from students as s left join grade as g ade = g.grade; 左连接 ,左表全显示，右表不满足null填充

4. select * from students as s right join grade as g ade = g.grade order by s.id; 右连接 ,右表全显示，左表不满足null填充

5. select * from students as s full join grade as g ade = g.grade order by s.id; 全连接查询，不满足相互null填充

3.27 任务 阅读 prefetch_pdf.py文件，写一个接口，保存pdf文件（分页码保存）

3.28 阅读argparse文档使用 import argparse 导包 parse = argparse.ArgumentParser() 创建解析处理器 parse.add_argument() 设置参数 args = parser.parse_args()  获取参数值 para = args.fullname

关于PostgreSQL的安装及使用

遇到的问题： 无法登陆：服务器是否启动，用户是否存在，数据库是否存在 无法启动：默认端口5432，启动下一个服务必须指定端口 用户和库存储到一个相同的目录：创建用户和数据库的时候需要指定端口，否则都存储到了同一个默认目录

PostgreSQL 初始化：initdb -D 路径 启动服务：pg_ctl start -D 路径 -o "-p port_number" -l 路径/postgresql.log 停止服务：pg_ctl stop -D 路径 -o "-p port_number" -m fast 创建用户指定端口：createuser -s user_name -h localhost -p port_number -P 创建数据库指定端口和归属：createdb dbname -O user_name -p port_number 登陆：psql -U user_name -d dbname -p port_number

initdb -D 路径/data/pg_data createuser username -P createdb dbname -O username -E UTF8 -e psql -h xxxxx -U dbuser -p port -d dbname

Postgresql 对象关系型数据库管理系统 []:可选 {}和|:必须选取一个候选 …:前面元素可重复 sql语句以 ；结尾 注意单双引号的使用 客户端和服务器模式，支持并发连接，所以，主服务器一直在等待连接，每一个postgres程序就是一个链接

安装：brew install postgresql 卸载：brew uninstall postgresql 启动：pg_ctl -D /usr/local/var/postgres -l /usr/local/var/postgres/server.log start jianhua$ pg_ctl start -D /Users/jianhua/Desktop/005/data/pg -o "-p 35331" -l /Users/jianhua/Desktop/005/data/pg/postgresql.log

停止：pg_ctl stop -D /Users/jianhua/Desktop/005/data/pg -o "-p 35331" -m fast

某一个端口的用户：

createuser -s jh01 -h localhost -p 35331 -P createdb jh01 -O jh01 -p 35331 psql -U jh01 -d jh01 -p 35331

创建数据库用户：create user username with password mima 创建数据库指定拥有者：create database dbname ower username 为拥有者添加权限：grant all privileges on database dbname to username

第二种方法：postgresql控制台 sudo add user Jianhua 新建Linux用户 sudo su - postgres 切换到postgres的用户 psql 登陆控制台 password postgres 为postgres用户设置密码 create user jianhua with password ‘密码’ 为刚才创建的Linux用户设置密码 create database jianhua01 owner jianhua 创建用户jianhua的数据库 grant all privileges on database jianhua01 to jianhua 为用户添加数据库权限 q 推出控制台 （ctrl+D 也行）

登陆数据库：psql命令 psql -U Jianhua -d jianhua01 -h 127.0.0.1 -p 5432

列举数据库：l -show databases 列举数据表：dt -show tables 查看表结构：d tbname -desc tbname 切换数据库：c dbname 创建数据库：createdb dbname -O username -E UTF8 -e 创建数据表：create table address_list（字段1 约束，字段2 约束。。。）；

慎用——— 删除数据库：drop database jianhua01 删除用户：drop user jianhua