postgresql 实践

1. 环境搭建

参考：http://www.lvesu.com/blog/main/cms-532.html

1.1. 安装依赖

# 需要安装 postgresql-devel 插件
yum install postgresql-devel*
# 安装 pg 和 py 的驱动：
# Debian系:
apt-get install libpq-dev python-dev
# RedHat系：
yum install libpqxx-devel python-devel

# 安装完成，再使用
pip install psycopg2
1
2
3
4
5
6
7
8
9
10

1.2. 安装postgres（包含 psql）

sudo apt-get -y install postgresql
# 启动服务
systemctl start postgresql.service
systemctl restart postgresql
1
2
3
4

1.3. 登录

1.3.1. 修改linux系统postgres用户的密码

PostgreSQL 都会创建一个默认的 linux 用户 postgres，修改该用户密码的方法如下：

1. 先删除用户 postgres 的历史密码

sudo  passwd -d postgres
1

2. 重新设置用户postgres的密码

sudo -u postgres passwd
1

按照系统提示，输入两次新的密码，即可搞定。

1.3.2. 修改用户postgres的密码

PostgreSQL 数据库默认创建管理员账号：postgres；修改其密码，仅需一下三步：

1. 首先，登录PostgreSQL

sudo -u postgres psql postgres -p 5432
1

2. 然后，修改账号 postgres 的密码

ALTER USER postgres WITH PASSWORD 'Lpf65BsDhDNdaJmH';
1

3. 最后，退出 pgsql 客户端

2. 数据库操作

2.1. 创建数据库

# 登录
psql -U postgres
# 执行sql
CREATE DATABASE manage;
# 查询
postgres=# \l;
# 选择数据库
postgres=# \c manage;
# 删除数据库
DROP DATABASE [ IF EXISTS ] name
DROP DATABASE IF EXISTS mydb;
1
2
3
4
5
6
7
8
9
10
11

2.2. 创建表

postgres=# CREATE TABLE questions(
	question_id varchar(32) PRIMARY KEY     NOT NULL,
	question_text      varchar(128)    NOT NULL,
	category      varchar(32)     NOT NULL,
	task_id varchar(32)      NOT NULL
);

postgres=# CREATE TABLE answers(
	answer_id varchar(32) PRIMARY KEY     NOT NULL,
	answer_text      varchar(128)    NOT NULL,
	question_id varchar(32)      NOT NULL,
	model_id varchar(32)      NOT NULL,
	task_id varchar(32)      NOT NULL,
	score INT
);
# 查看所有表
postgres=# \dt
# 查看指定表，显示表信息
postgres=# \d answers

# 删除表
DROP TABLE [ IF EXISTS ] name
DROP TABLE IF EXISTS answers;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23

2.3. 多表查询

https://blog.csdn.net/weixin_67588007/article/details/124832788

2.3.1. 通过 SELECT 子句进行多表查询

语法：
select 字段名
from 表1，表2 …
where 表1.字段 = 表2.字段
and 其它查询条件
例：以学生表 student 和班级表 class 为例
Select student.sid, student.sname, student.classid, class.classid, class.classname
from student，class
where student.classid = class.classid
注意：上面的代码中，以两张表相同的字段信息作为条件，进行两个表联查，但在实际开发中不建议这样使用，最好用主外键约束来实现。

2.3.2. 通过内连接 inner join 进行查询

语法：
select 字段名
from 表1
inner join 表2
on 表1.字段 = 表2.字段
例：以学生表 student 和班级表 class 为例

select student.sid, student.sname, student.classid, class.classid, class.classname
from student
inner join class
on student.classid = class.classid

这种场景下得到的是满足判断条件的 student，class 内部的数据；正因为得到的是内部共有数据，所以连接方式称为内连接。

2.3.3. 通过外连接left join，left outer join，right join，right outer join，union进行查询

2.3.3.1. left join

语法：
select 字段名
from 表1
left join 表2
on 表1.字段 = 表2.字段
例：以学生表 student 和班级表 class 为例

select student.* , class.*
from student
left join class
on student.classid = class.classid
结果如下，class表中不存在的记录填充Null:

这种场景下得到的是 student 的所有数据，和满足同一条件的 class 的数据;

2.3.3.2. left outer join

相当于left join + [where 表2.字段 is null]
语法：
select 字段名
from 表1
left join 表2
on 表1.字段 = 表2.字段
where 表2.字段 is null
例：以学生表student和班级表class为例

select student.sid，student.sname，class.classid，class.classname
from student
left join class
on student.classid = class.classid
where class.classid is null

这种场景下得到的是student中的所有数据减去"与 class 满足同一条件 的数据"，然后得到的student剩余数据

2.3.3.3. right join

语法：
select 字段名
from 表1
right join 表2
on 表1.字段 = 表2.字段
例：以学生表student和班级表class为例

select student.* , class.*
from student
right join class
on student.classid = class.classid

这种场景下得到的是 class 的所有数据，和满足同一条件的 student 的数据；

2.3.3.4. right outer join

相当于right join + [where 表1.字段 is null]
语法：
select 字段名
from 表1
right join 表2
on 表1.字段 = 表2.字段
where 表1.字段 is null
例：以学生表student和班级表class为例

select student.sid，student.sname，class.classid，class.classname
from student
right join class
on student.classid = class.classid
where student.classid is null

这种场景下得到的是 class 中的所有数据减去 "与 student 满足同一条件的数据“，然后得到的 class 剩余数据；

2.3.3.5. left join union right join

语法：
select 字段名
from 表1
left join 表2
on 表1.字段 = 表2.字段
union
select 字段名
from 表1
right join 表2
​​​​​​​on 表1.字段 = 表2.字段
例：以学生表 student 和班级表 class 为例

select student.* , class.*
from student
left join class
on student.classid = class.classid
union
select student.* , class.*
from student
right join class
on student.classid = class.classid

这种场景下得到的是满足某一条件的公共记录，和独有的记录

3. psycopg2 连接数据库

3.1. 连接数据库

使用 psycopg2 模块连接到 PostgreSQL 数据库。能够使用以下连接方法执行所有查询。现在我想指定一个与 public 不同的模式来执行我的 SQL 语句。有没有办法在连接方法中指定模式名称？

conn = psycopg2.connect(host="localhost",
                            port="5432",
                            user="postgres",
                            password="password",
                            database="database",
                            )
1
2
3
4
5
6

我尝试直接在方法内部指定架构。 schema=“schema2” 但我收到以下编程错误。
ProgrammingError: invalid dsn: invalid connection option “schema”

3.2. 批量插入

'''批量插入sql语句'''
import pymysql,string,random,time
def connet_mysql():
    try:
        db=pymysql.connect(host='192.168.31.103',user='root',password='123456',
                           db='test',port=3306)
    except Exception as e:
        print('数据库连接失败',e)
    return db

def insert_data(id,username,password):
    db=connet_mysql()
    cursor=db.cursor()
    **sql_1='insert into user_test(id,user,password) values (%s,%s,%s)'**
    sql_2='select * from user_test'
    # 使用元组插入
    **params=(id,username,password)**
    **cursor.execute(sql_1,params)**
    cursor.execute(sql_2)
    db.commit()
    all=cursor.fetchall()#通过游标，获取查询内容
    print(all)

def info():
    str_1d=string.digits
    str_2a=string.ascii_letters
    str_3=str_1d+str_2a
    for i in range(501,601):
        id=i
        username='user'+str(i)
        password=''.join(random.sample(str_3,6))
        insert_data(id,username,password)
if __name__ == '__main__':
    info()
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34

4. 数据库连接池

安装：
https://blog.csdn.net/weixin_44041700/article/details/110454901

使用：
https://blog.51cto.com/u_16213421/7115953
https://www.cnblogs.com/xy-ouyang/p/12987676.html