PythonWeb - #002 操作PostgreSQL（CRUD）

操作PostgreSQL（CRUD）

1 目标

本文的目的是使用python操作PostgreSQL实现增删改查。

2 步骤

2.1 前提条件

• 安装扩展
  
• 执行sql语句

CREATE EXTENSION postgis;

2.2 代码

安装依赖包psycopg2

2.2.1 创建表（带空间数据）

import psycopg2
from psycopg2 import sql

# 数据库连接配置
db_config = {
    "host": "localhost",
    "port": 5432,
    "user": "postgres",
    "password": "",
    "database": "postgres"
}

# 创建表
def create_table():
    create_table_query = """
    CREATE TABLE contacts (
        id SERIAL PRIMARY KEY,
        name VARCHAR(100),
        tel VARCHAR(20),
        geometry GEOMETRY
    );
    """
    try:
        with psycopg2.connect(**db_config) as conn:
            with conn.cursor() as cur:
                cur.execute(create_table_query)
                conn.commit()
                print('Table "contacts" created successfully')
    except Exception as err:
        print("Error creating table:", err)

2.2.2 查询表

2.2.2.1 属性查询

2.2.2.1.1 裸属性查询

def query_data_by_name(name):
    query = "SELECT * FROM contacts WHERE name = %s"
    try:
        with psycopg2.connect(**db_config) as conn:
            with conn.cursor() as cur:
                cur.execute(query, (name,))
                rows = cur.fetchall()
                print('Attribute Query Result:', rows)
    except Exception as err:
        print(err)

2.2.2.1.2 格式化空间坐标属性查询

def query_data_by_name_with_coordinates(name):
    query = "SELECT id, name, tel, ST_AsText(geometry) as geometry FROM contacts WHERE name = %s"
    try:
        with psycopg2.connect(**db_config) as conn:
            with conn.cursor() as cur:
                cur.execute(query, (name,))
                rows = cur.fetchall()
                print("Attribute Query Result:", rows)
    except Exception as err:
        print(err)

2.2.2.2 空间查询

2.2.2.2.1 矩形查询

def query_data_by_bounding_box(bbox):
    query = "SELECT * FROM contacts WHERE ST_Within(geometry, ST_MakeEnvelope(%s, %s, %s, %s, 4326))"
    values = (bbox['minLon'], bbox['minLat'], bbox['maxLon'], bbox['maxLat'])
    try:
        with psycopg2.connect(**db_config) as conn:
            with conn.cursor() as cur:
                cur.execute(query, values)
                rows = cur.fetchall()
                print("Spatial Query Result (Bounding Box):", rows)
    except Exception as err:
        print(err)

2.2.2.2.2 多边形查询

def query_data_by_polygon(polygon):
    query = "SELECT * FROM contacts WHERE ST_Within(geometry, ST_GeomFromText(%s, 4326))"
    try:
        with psycopg2.connect(**db_config) as conn:
            with conn.cursor() as cur:
                cur.execute(query, (polygon,))
                rows = cur.fetchall()
                print("Spatial Query Result (Polygon):", rows)
    except Exception as err:
        print(err)

2.2.3 修改表

2.2.3.1 修改属性

def update_name(old_name, new_name):
    query = "UPDATE contacts SET name = %s WHERE name = %s RETURNING *"
    try:
        with psycopg2.connect(**db_config) as conn:
            with conn.cursor() as cur:
                cur.execute(query, (new_name, old_name))
                conn.commit()
                rows = cur.fetchall()
                print("Update Name Result:", rows)
    except Exception as err:
        print(err)

2.2.3.2 修改几何对象

def update_geometry(name, new_geometry):
    query = "UPDATE contacts SET geometry = ST_GeomFromText(%s, 4326) WHERE name = %s RETURNING *"
    try:
        with psycopg2.connect(**db_config) as conn:
            with conn.cursor() as cur:
                cur.execute(query, (new_geometry, name))
                conn.commit()
                rows = cur.fetchall()
                print('Update Geometry Result:', rows)
    except Exception as err:
        print(err)

2.2.4 删除记录

def delete_data_by_name(name):
    query = "DELETE FROM contacts WHERE name = %s RETURNING *"
    try:
        with psycopg2.connect(**db_config) as conn:
            with conn.cursor() as cur:
                cur.execute(query, (name,))
                conn.commit()
                rows = cur.fetchall()
                print("Delete Result:", rows)
    except Exception as err:
        print(err)

2.2.5 全部代码

import psycopg2
from psycopg2 import sql

# 数据库连接配置
db_config = {
    "host": "localhost",
    "port": 5432,
    "user": "postgres",
    "password": "",
    "database": "postgres"
}

# 创建表
def create_table():
    create_table_query = """
    CREATE TABLE contacts (
        id SERIAL PRIMARY KEY,
        name VARCHAR(100),
        tel VARCHAR(20),
        geometry GEOMETRY
    );
    """
    try:
        with psycopg2.connect(**db_config) as conn:
            with conn.cursor() as cur:
                cur.execute(create_table_query)
                conn.commit()
                print('Table "contacts" created successfully')
    except Exception as err:
        print("Error creating table:", err)

# 按名称查询数据
def query_data_by_name(name):
    query = "SELECT * FROM contacts WHERE name = %s"
    try:
        with psycopg2.connect(**db_config) as conn:
            with conn.cursor() as cur:
                cur.execute(query, (name,))
                rows = cur.fetchall()
                print('Attribute Query Result:', rows)
    except Exception as err:
        print(err)

# 按名称查询数据并转换几何为坐标
def query_data_by_name_with_coordinates(name):
    query = "SELECT id, name, tel, ST_AsText(geometry) as geometry FROM contacts WHERE name = %s"
    try:
        with psycopg2.connect(**db_config) as conn:
            with conn.cursor() as cur:
                cur.execute(query, (name,))
                rows = cur.fetchall()
                print("Attribute Query Result:", rows)
    except Exception as err:
        print(err)

# 按边界框查询数据
def query_data_by_bounding_box(bbox):
    query = "SELECT * FROM contacts WHERE ST_Within(geometry, ST_MakeEnvelope(%s, %s, %s, %s, 4326))"
    values = (bbox['minLon'], bbox['minLat'], bbox['maxLon'], bbox['maxLat'])
    try:
        with psycopg2.connect(**db_config) as conn:
            with conn.cursor() as cur:
                cur.execute(query, values)
                rows = cur.fetchall()
                print("Spatial Query Result (Bounding Box):", rows)
    except Exception as err:
        print(err)

# 按多边形查询数据
def query_data_by_polygon(polygon):
    query = "SELECT * FROM contacts WHERE ST_Within(geometry, ST_GeomFromText(%s, 4326))"
    try:
        with psycopg2.connect(**db_config) as conn:
            with conn.cursor() as cur:
                cur.execute(query, (polygon,))
                rows = cur.fetchall()
                print("Spatial Query Result (Polygon):", rows)
    except Exception as err:
        print(err)

# 更新名称
def update_name(old_name, new_name):
    query = "UPDATE contacts SET name = %s WHERE name = %s RETURNING *"
    try:
        with psycopg2.connect(**db_config) as conn:
            with conn.cursor() as cur:
                cur.execute(query, (new_name, old_name))
                conn.commit()
                rows = cur.fetchall()
                print("Update Name Result:", rows)
    except Exception as err:
        print(err)

# 更新几何
def update_geometry(name, new_geometry):
    query = "UPDATE contacts SET geometry = ST_GeomFromText(%s, 4326) WHERE name = %s RETURNING *"
    try:
        with psycopg2.connect(**db_config) as conn:
            with conn.cursor() as cur:
                cur.execute(query, (new_geometry, name))
                conn.commit()
                rows = cur.fetchall()
                print('Update Geometry Result:', rows)
    except Exception as err:
        print(err)

# 按名称删除数据
def delete_data_by_name(name):
    query = "DELETE FROM contacts WHERE name = %s RETURNING *"
    try:
        with psycopg2.connect(**db_config) as conn:
            with conn.cursor() as cur:
                cur.execute(query, (name,))
                conn.commit()
                rows = cur.fetchall()
                print("Delete Result:", rows)
    except Exception as err:
        print(err)

# 示例调用
if __name__ == "__main__":
    create_table()
    query_data_by_name('John Doe')
    query_data_by_name_with_coordinates('Alice Johnson')
    bbox = { 'minLon': 5, 'minLat': 5, 'maxLon': 50, 'maxLat': 50 }
    query_data_by_bounding_box(bbox)
    polygon = "POLYGON((5 5, 5 50, 50 50, 50 5, 5 5))"
    query_data_by_polygon(polygon)
    update_name('Jane Smith', 'Tom')
    us_polygon = 'POLYGON((-125.001650 24.9493, -66.9326 24.9493, -66.9326 49.5904, -125.001650 49.5904, -125.001650 24.9493))'
    update_geometry('Alice Johnson', us_polygon)
    delete_data_by_name('Tom')