文章目录
- 操作PostgreSQL(CRUD)
- 1 目标
- 2 步骤
- 2.1 前提条件
- 2.2 代码
- 2.2.1 创建表(带空间数据)
- 2.2.2 查询表
- 2.2.2.1 属性查询
- 2.2.2.1.1 裸属性查询
- 2.2.2.1.2 格式化空间坐标属性查询
- 2.2.2.2 空间查询
- 2.2.2.2.1 矩形查询
- 2.2.2.2.2 多边形查询
- 2.2.3 修改表
- 2.2.3.1 修改属性
- 2.2.3.2 修改几何对象
- 2.2.4 删除记录
- 2.2.5 全部代码
操作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')