Postgresql数据库之Python连接数据库&查询练习

Task 1. 基于finalshell建立的SSH隧道，实现Python代码连接天翼云数据库

（1）给出finalshell的配置

如下图：

为了登录安全起见，将ssh登录端口和数据库监听端口进行了修改。

（2）给出Python连接天翼云数据库的代码

Python代码如下：

import psycopg2
conn = psycopg2.connect( dbname = 'a2513210112',                        
						 user = '*******',                        
                        password = '*******',                        
                        host = '127.0.0.1',                        
                        port = 9929,                        
                        options = '-c search_path="S-T",public')
print("connection is ready")

为了数据库安全，将user和password进行了隐藏处理，连接时并指定连接“S-T”模式下。

（1）给出查询上次实验中建立的“S-T”模式中，student表中所有的数据，并在界面打印出来

Python代码如下：

import psycopg2

conn = psycopg2.connect(dbname = 'a2513210112',
                        user = '*******',
                        password = '*******',
                        host = '127.0.0.1',
                        port = 9929,
                        options = '-c search_path="S-T",public')

#print("connection is ready")

cur = conn.cursor()
cur.execute("SELECT * FROM student;")
rows = cur.fetchall()
for row in rows:
    print(row)
cur.close()
conn.close()

Task2 根据给定的excel表格中的表结构信息，建三个表，选择在S-Tschema下建表

--部门建表
CREATE TABLE dept 
(
deptno INT PRIMARY KEY,
dname VARCHAR(20),
loc VARCHAR(20)
);
--员工建表
CREATE TABLE emp
(
empno INT PRIMARY KEY,
ename VARCHAR(20),
job VARCHAR(20),
mgr INT,
hiredate DATE,
sal FLOAT(16),
comm FLOAT(16),
deptno INT,
FOREIGN KEY (deptno) REFERENCES dept(deptno)
);
CREATE TABLE salgrade
(
grade INT,
losal FLOAT(16),
hisal FLOAT(16)
);

Task3.写Python代码，嵌入SQL语句，将给的excel表格中的数据，导入数据库

（1）通过python连接云上数据库，并执行嵌入的sql脚本和完整可执行程序

import psycopg2

conn = psycopg2.connect(dbname = 'a2513210112',
                        user = '*******',
                        password = '*******',
                        host = '127.0.0.1',
                        port = 9929,
                        options = '-c search_path="S-T",public')

print("connection is ready")
cur = conn.cursor()#获取游标
#对部门表插入数据
sql="insert into dept values(%s,%s,%s)"
# 每一个值都作为一个元组，整个参数集作为一个元组
param=((10,'ACCOUNTING','NEW YORK'),
        (20,'RESEARCH','DALLAS'),
       (30,'SALES','CHICAGO'),
       (40,'OPERATIONS','BOSTON')
)
cur.executemany(sql,param)
# 提交


conn.commit()
conn.commit()
cur.execute("select * from dept")
rows = cur.fetchall()
for row in rows:
    print(row)

执行结果：

#对员工表插入数据
sql = "insert into emp values(%s,%s,%s,%s,%s,%s,%s,%s)"
param =( (7369,'SMITH', 'CLERK',7902,'1980-12-17',800,None,20),
         (7499,'ALLEN','SALESMAN',7698,'1981-02-20',1600,300,30),
         (7521,'WARD',  'SALESMAN',    7698,'1981-02-22',1250,500,30),
         (7566,'JONES', 'MANAGER', 7839,  '1981-04-02',2975,None,20),
         (7654,'MARTIN',    'SALESMAN',7698,'1981-09-28',1250,1400,30),
         (7698, 'BLAKE',   'MANAGER', 7839,  '1981-05-01',  2850,None,30),
         (7782, 'CLARK','MANAGER',7839 ,'1981-06-09', 2450,None,10),
         (7788, 'SCOTT','ANALYST', 7566,  '1987-07-13',  3000,None,20),
         (7839, 'KING',    'PRESIDENT',None,'1981-11-07', 5000,None,10),
         (7844, 'TURNER','SALESMAN',   7698,  '1981-09-08',  1500,  0, 30),
         (7876, 'ADAMS',   'CLERK',7788,  '1987-07-13',  1100,None,20),
         (7900, 'JAMES','CLERK',   7698,  '1981-12-03',950   ,None,30),
         (7902, 'FORD','ANALYST',7566, '1981-12-03',3000,None,20),
         (7934, 'MILLER','CLERK',7782,'1982-01-23',1300,None,10)
)
cur.executemany(sql,param)
# 提交
conn.commit()
cur.execute("select * from emp")
rows = cur.fetchall()
for row in rows:
    print(row)

sql = "insert into salgrade values(%s,%s,%s)"
param = (
    (1, 700,   1200),
    (2, 1201,  1400),
    (3, 1401,  2000),
    (4, 2001,  3000),
    (5, 3001,  9999)
)
cur.executemany(sql,param)
# 提交
conn.commit()
cur.execute("select * from salgrade")
rows = cur.fetchall()
for row in rows:
    print(row)

（2）查询结果

SELECT * FROM emp;
SELECT * FROM dept;
SELECT * FROM salgrade;

Task 4. 根据问题，写SQL代码，实现相应的操作。

4.1列出与“SCOTT”从事相同工作的所有员工及部门名称，部门人数。

SELECT emp.ename,cn.dname, cn.部门人数
FROM emp 
JOIN (SELECT emp.deptno ,dname, COUNT(empno) "部门人数" 
					FROM emp , dept  
					WHERE emp.deptno = dept.deptno 
					GROUP BY emp.deptno, dept.dname) cn
ON  emp.deptno = cn.deptno AND job = (select job from emp where ename = 'SCOTT') AND emp.ename <> 'SCOTT'

4.2列出薪金高于"在部门30工作的所有员工的薪金"的员工姓名和薪金、部门名称。

SELECT e.ename,e.sal,d.dname
FROM emp e
JOIN dept d
ON e.deptno = d.deptno 
WHERE sal > ALL(SELECT sal FROM emp WHERE deptno = 30);

4.3列出每个部门的员工数量、平均工资和平均服务期限。

按年：

import psycopg2

conn = psycopg2.connect(dbname = 'a2513210112',
                        user = '*******',
                        password = '*******',
                        host = '127.0.0.1',
                        port = 9929,
                        options = '-c search_path="S-T",public')

print("connection is ready")
cur = conn.cursor()#获取游标
cur.execute("select deptno,COUNT(empno),avg(sal) as 平均薪资 ,avg(2023 - extract(year from hiredate))  from emp group by deptno ")
rows = cur.fetchall()
for row in rows:
     print(row)
cur.close()
conn.close()

按天：

import psycopg2

conn = psycopg2.connect(dbname = 'a2513210112',
                        user = '*******',
                        password = '*******',
                        host = '127.0.0.1',
                        port = 9929,
                        options = '-c search_path="S-T",public')

print("connection is ready")
cur = conn.cursor()#获取游标
cur.execute("select deptno, COUNT(empno),AVG(sal) as 平均薪资, AVG(CURRENT_DATE - hiredate)  from emp group by deptno ")
rows = cur.fetchall()
for row in rows:
     print(row)
cur.close()
conn.close()

4.4列出所有员工的姓名、部门名称和工资。

import psycopg2

conn = psycopg2.connect(dbname = 'a2513210112',
                        user = '*******',
                        password = '*******',
                        host = '127.0.0.1',
                        port = 9929,
                        options = '-c search_path="S-T",public')

print("connection is ready")
cur = conn.cursor()#获取游标
cur.execute("SELECT e.ename,d.dname,e.sal FROM emp e JOIN dept d ON e.deptno = d.deptno ")
rows = cur.fetchall()
for row in rows:
     print(row)
cur.close()
conn.close()

4.5列出所有部门的详细信息和部门人数。

import psycopg2

conn = psycopg2.connect(dbname = 'a2513210112',
                        user = '*******',
                        password = '*******',
                        host = '127.0.0.1',
                        port = 9929,
                        options = '-c search_path="S-T",public')

print("connection is ready")
cur = conn.cursor()
cur.execute("select d.*, b.cn from dept d  left join (select deptno , COUNT(*) cn from emp group by deptno) b on d.deptno = b.deptno")
rows = cur.fetchall()
for row in rows:
     print(row)
cur.close()
conn.close()

4.6列出各种工作的最低工资及从事此工作的雇员姓名。

SELECT  e.job ,e.sal ,e.ename 
FROM emp e
WHERE sal IN  (SELECT min(sal) FROM emp GROUP BY job);

4.7列出各个部门的MANAGER(经理)的最低薪金、姓名、部门名称、部门人数。

SELECT e.ename, d.dname,dc.cn,ms.min_sal 最低薪金
 FROM emp e
 JOIN dept d 
 ON d.deptno = e.deptno 
 JOIN (SELECT deptno, MIN(sal) min_sal FROM emp WHERE job = 'MANAGER' GROUP BY deptno) ms
 ON ms.deptno = e.deptno 
 JOIN (select deptno , COUNT(*) cn from emp group by deptno) dc
 ON e.deptno = dc.deptno  AND e.job = 'MANAGER'

4.8列出所有员工的姓名以及其直接上级的姓名

SELECT e0.ename,e1.ename 上级
FROM emp e0
JOIN emp e1
ON e0.mgr =e1.empno

4.9列出受雇日期早于其直接上级的所有员工的编号、姓名，部门名称

select e.empno , e.ename , d.dname
from emp e 
join dept d 
on e.deptno = d.deptno
left join emp e1 
on e.mgr = e1.empno
where e.hiredate < e1.hiredate;