Python代码片段之SQL操作

连接 PostgreSQL

# 连接 PostgreSQL
import psycopg2
import pandas as pd

connection = psycopg2.connect(user = "username",
                                  password = "password",
                                  host = "127.0.0.1",
                                  port = "5432",
                                  database = "dn_name")
cursor = connection.cursor()

test_query = """SELECT subject_id, hadm_id, admittime, dischtime, admission_type, diagnosis
FROM admissions
"""

test = pd.read_sql_query(test_query, connection)

单表导出数据

# 单表导出数据
## 从单表中获取数据
## 使用 WHERE 和 HAVING 语句进行过滤
## 用 order_by_list 字段进行降序排序
## conn 为 postgreSQL 的数据库连接对象
query_string = """SELECT [DISTINCT] <select_column_list> [AS <alias_name>]
FROM <table_name>
WHERE <where_condition_1> AND <where_condition_2> OR <where_condition_3>
HAVING <having_condition>
ORDER BY <order_by_list> [DESC]
"""

df = pd.read_sql_query(query_string, conn)

使用 WHERE 语句过滤

# 使用 WHERE 语句过滤
## 从单表中获取数据
## 使用 WHERE 语句进行过滤，包含了 IN 包含关系，和使用 LIKE 做模式匹配
## conn 为 postgreSQL 的数据库连接对象
query_string = """SELECT [DISTINCT] <select_column_list> [AS <alias_name>]
FROM <table_name>
WHERE column_name [NOT] IN ( value_1, value_2, ...,value_n)
AND column_name BETWEEN value_1 AND value_2
OR column_name LIKE 'string'
"""

df = pd.read_sql_query(query_string, conn)

使用 HAVING 语句过滤

# 使用 HAVING 语句过滤
## 从单表中获取数据
## 使用 HAVING 语句作过滤，包含聚合函数
## conn 为 postgreSQL 的数据库连接对象
query_string = """SELECT [DISTINCT] <select_column_list> [AS <alias_name>]
FROM <table_name>
HAVING [aggregation function] = value_1
AND [aggregation_function] = value_2
"""

df = pd.read_sql_query(query_string, conn)

取出前N条数据

# 取出前N条数据
## 从单表中获取数据
## 使用 WHERE 和 HAVING 语句进行过滤
## 用 order_by_list 字段进行降序排序
## 取出头部的N条数据
## conn 为 postgreSQL 的数据库连接对象
query_string = """SELECT [DISTINCT] <select_column_list> [AS <alias_name>]
FROM <table_name>
WHERE <where_condition_1> AND <where_condition_2> OR <where_condition_3>
HAVING <having_condition>
ORDER BY <order_by_list> [DESC]
LIMIT <selected N>
"""

df = pd.read_sql_query(query_string, conn)

多表导出数据

# 多表导出数据
## 从多表中获取数据
## 使用 INNER JOIN 从两表中获取数据
## 使用 WHERE 和 HAVING 语句进行过滤
## conn 为 postgreSQL 的数据库连接对象
query_string = """SELECT [DISTINCT] <select_column_list>
FROM <left_table>
<join_type> JOIN <right_table>
ON <join_condition>  
WHERE <where_condition>
HAVING <having_condition>
ORDER BY <order_by_list> DESC
LIMIT <limit_number>
"""

df = pd.read_sql_query(query_string, conn)

使用聚合函数

# 使用聚合函数
## 从多表中获取数据
## 使用 INNER JOIN 从两表中获取数据
## 使用 WHERE 和 HAVING 语句进行过滤
## conn 为 postgreSQL 的数据库连接对象
query_string = """SELECT [aggregation function] (<column_name>)
FROM <left_table>
<join_type> JOIN <right_table>
ON <join_condition>
WHERE <where_condition>
GROUP BY <group_by_all_the_left_columns>
[HAVING <having_condition>]
[ORDER BY <order_by_list>]
[LIMIT <limit_number>]
"""

df = pd.read_sql_query(query_string, conn)

使用 Subquery

# 使用 Subquery
## 如果SQL Query过长过复杂，可把Subquery的结果进行调用
## conn 为 postgreSQL 的数据库连接对象

query_string = """--subquery作为一张表
SELECT <column_list> FROM ( 
  SELECT <column_list> FROM table_name
) AS alias_table_name
"""
query_string_1 = """--subquery作为一系列值
SELECT <column_list> FROM <table_name>
WHERE <column_name> IN (
  SELECT <column_name> FROM <table_name> WHERE  <where_condition>
)
"""

df = pd.read_sql_query(query_string, conn)

使用 WITH 语句

# 使用 WITH 语句
## 如果SQL Query过长过复杂，可以将子查询的结果定义为表变量在后期复用
## conn 为 postgreSQL 的数据库连接对象
query_string = """WITH table_variable_1 AS (
  <SELECT query>
),

table_variable_2 AS (
  SELECT * FROM table_variable_1;
)

SELECT * FROM table_variable_2；
"""

df = pd.read_sql_query(query_string, conn)

通用条件表达式

# 通用条件表达式 (类似if-else)
## 条件语句
## conn 为 postgreSQL 的数据库连接对象
query_string = """-- 通用条件表达式 (类似if-else)
CASE  
WHEN condition1 THEN result1 -- i.e WHEN count > 5 THEN 1
WHEN condition2 THEN result2 -- i.e. WHEN name = 'elle' THEN 'ELLE'
[...]
[ELSE result_n]
END 
"""

df = pd.read_sql_query(query_string, conn)

查看数据库中所有表名

## 查看数据库中所有表名
## conn 为 postgreSQL 的数据库连接对象
query_string = """
SELECT * FROM pg_tables
WHERE schemaname <> 'pg_catalog' AND schemaname <> 'information_schema'; 
"""

df = pd.read_sql_query(query_string, conn)

窗口函数

# 窗口函数
## 窗口函数
## conn 为 postgreSQL 的数据库连接对象
query_string = """SELECT <<column_name>,
       window_func() OVER ( [PARTITION BY xx] [ORDER BY xx] )
FROM <table_name>
"""

df = pd.read_sql_query(query_string, conn)