引言
在现代软件开发和数据管理领域中,数据库操作是至关重要的一部分。借助于强大的开源工具如pgAdmin,我们能够更轻松地管理和操作PostgreSQL数据库。本文将为大家呈现一个基于Python的实用示例,展示如何利用pgAdmin进行数据库的基本操作。通过这个示例,我们将了解如何连接数据库、执行查询、更新数据以及其他关键的数据库管理任务,帮助大家在开发过程中更高效地处理数据。让我们一同深入探索如何借助Python与pgAdmin无缝协作,优化数据库操作流程。
pgAdmin是一款强大的开源数据库管理工具,具有许多优点,使其成为许多开发者和数据库管理员首选的工具之一。以下是一些pgAdmin的主要优点:
- 用户友好的界面: pgAdmin提供直观且易于使用的图形用户界面,使用户能够轻松地浏览、管理和操作数据库。
- 多平台支持: pgAdmin可在多个操作系统上运行,包括Windows、Linux和macOS,使其成为一个跨平台的数据库管理解决方案。
- 开源免费: 作为开源软件,pgAdmin可以免费获取和使用,这对于那些希望在有限预算下进行数据库管理的团队和开发者而言是一个重要的优势。
- 支持多个数据库: pgAdmin不仅支持PostgreSQL数据库,还支持其他一些主流数据库系统,如MySQL、MariaDB、SQLite等,使其成为一个多数据库管理工具。
- 强大的功能集: pgAdmin提供了丰富的功能集,包括数据库对象的创建和管理、SQL查询的执行、备份和恢复数据库、用户权限管理等,满足了各种数据库管理任务的需求。
- 扩展性: pgAdmin允许用户通过插件扩展其功能,以满足特定的需求和定制化要求,从而更好地适应各种工作流程。特别是可以安装扩展
postgis
,使得PostgreSQL
数据库能够处理空间和地理对象数据。 - 实时监控和性能优化: pgAdmin提供实时监控功能,可以追踪数据库的性能和健康状况,帮助用户进行性能优化和故障排除。
定义操作函数
创建数据库
def create_db(database):
# 连接到默认的postgres数据库
try:
conn = psycopg2.connect(**db_connection) # 连接数据库
except:
print('数据库连接失败!')
return
conn.set_isolation_level(ISOLATION_LEVEL_AUTOCOMMIT) # 设置自动提交事务
# 创建一个游标对象
cur = conn.cursor()
# 执行创建数据库的SQL语句
try:
create_database_query = f"CREATE DATABASE {database}"
cur.execute(create_database_query)
except:
print('数据库创建失败!')
return
# 提交事务
conn.commit()
# 关闭游标和连接
cur.close()
conn.close()
# 打印提示信息
print(f'{database}数据库创建成功!')
删除数据库
def delete_db(database):
# 连接到默认的postgres数据库
try:
conn = psycopg2.connect(**db_connection) # 连接数据库
except:
print('数据库连接失败!')
return
conn.set_isolation_level(ISOLATION_LEVEL_AUTOCOMMIT) # 设置自动提交事务
# 创建一个游标对象
cur = conn.cursor()
# 执行删除数据库的SQL语句
try:
delete_database_query = f"DROP DATABASE {database}"
cur.execute(delete_database_query)
except:
print('数据库删除失败!')
return
# 提交事务
conn.commit()
# 关闭游标和连接
cur.close()
conn.close()
# 打印提示信息
print(f'{database}数据库删除成功!')
在database下创建table_name表
def create_tb(database, table_name, columns):
# 连接到数据库
try:
conn = psycopg2.connect(database=database, **db_connection) # 连接数据库
except:
print('数据库连接失败!')
return
conn.set_isolation_level(ISOLATION_LEVEL_AUTOCOMMIT) # 设置自动提交事务
# 构建列定义部分
column_defs = ", ".join(columns)
# 创建一个游标对象
cur = conn.cursor()
# 执行创建表的SQL语句
try:
create_table_query = f"CREATE TABLE {table_name} ({column_defs});"
cur.execute(create_table_query)
except:
print('表创建失败!\n表名不符合要求表已存在!')
return
# 提交事务
conn.commit()
# 关闭游标和连接
cur.close()
conn.close()
# 打印提示信息
print(f'{database}:{table_name}表创建成功!')
删除database下的table_name表
def delete_tb(database, table_name):
# 连接到数据库
try:
conn = psycopg2.connect(database=database, **db_connection) # 连接数据库
except:
print('数据库连接失败!')
return
conn.set_isolation_level(ISOLATION_LEVEL_AUTOCOMMIT) # 设置自动提交事务
# 创建一个游标对象
cur = conn.cursor()
# 执行删除表的SQL语句
try:
delete_table_query = f"DROP TABLE {table_name}"
cur.execute(delete_table_query)
except:
print('表删除失败!\n表名不符合要求或表不存在!')
return
# 提交事务
conn.commit()
# 关闭游标和连接
cur.close()
conn.close()
# 打印提示信息
print(f'{database}:{table_name}表删除成功!')
查看所有数据库名
def view_db():
# 连接到默认的postgres数据库
try:
conn = psycopg2.connect(**db_connection) # 连接数据库
except:
print('数据库连接失败!')
return
# 创建一个游标对象
cur = conn.cursor()
# 执行查看所有数据库的SQL语句
try:
view_database_query = f"SELECT datname FROM pg_database;"
cur.execute(view_database_query) # 执行SQL语句
databases = cur.fetchall() # 获取所有数据库
except:
print('数据库名查询失败!')
return
# 关闭游标和连接
cur.close()
conn.close()
# 打印提示信息
print('数据库名查询完成!')
database_names = [database[0] for database in databases]
return database_names
查看database下所有表名
'''查看database下所有表名'''
def view_tb(database):
# 连接到数据库
try:
conn = psycopg2.connect(database=database, **db_connection) # 连接数据库
except:
print('数据库连接失败!')
return
# 创建一个游标对象
cur = conn.cursor()
# 执行查看所有表名的SQL语句
try:
view_table_query = "SELECT table_name FROM information_schema.tables WHERE table_schema='public';"
cur.execute(view_table_query) # 执行SQL语句
tables = cur.fetchall() # 获取所有表名
except:
print('表名查询失败!')
return
# 关闭游标和连接
cur.close()
conn.close()
# 打印提示信息
print(f'{database}:表名查询完成!')
table_names = [table[0] for table in tables]
return table_names
查看database下table_name的表结构
def tb_structure(database, table_name):
# 连接到数据库
try:
conn = psycopg2.connect(database=database, **db_connection) # 连接数据库
except:
print('数据库连接失败!')
return
# 创建一个游标对象
cur = conn.cursor()
# 执行查看表结构的SQL语句
try:
view_table_query = f"SELECT column_name, data_type FROM information_schema.columns WHERE table_name = '{table_name}';"
cur.execute(view_table_query) # 执行SQL语句
columns = cur.fetchall() # 获取所有列名
except:
print('表结构查询失败!')
return
# 关闭游标和连接
cur.close()
conn.close()
# 打印提示信息
print(f'{database}:{table_name}表结构查询完成!')
return columns
将DataFrame数据写入database下的table_name表
def insert_df(database, table_name, df):
# 连接到数据库
try:
conn = psycopg2.connect(database=database, **db_connection) # 连接数据库
except:
print('数据库连接失败!')
return
# 创建一个游标对象
cur = conn.cursor()
# 构建插入数据的 SQL 语句
columns = df.columns.tolist()
values = [tuple(row) for row in df.values]
insert_query = sql.SQL("INSERT INTO {table} ({columns}) VALUES {values}").format(
table=sql.Identifier(table_name),
columns=sql.SQL(', ').join(map(sql.Identifier, columns)),
values=sql.SQL(', ').join(map(sql.Literal, values))
)
# 执行插入操作
try:
cur.execute(insert_query)
except Exception as e:
print('插入数据失败:', e)
return
# 提交事务
conn.commit()
# 关闭游标和连接
cur.close()
conn.close()
# 打印提示信息
print('数据插入成功!')
查看database下table_name的表数据
def tb_data(database, table_name):
# 连接到数据库
try:
conn = psycopg2.connect(database=database, **db_connection) # 连接数据库
except:
print('数据库连接失败!')
return
# 创建一个游标对象
cur = conn.cursor()
# 执行查看表数据的SQL语句
try:
view_table_query = f"SELECT * FROM {table_name};"
cur.execute(view_table_query) # 执行SQL语句
data = cur.fetchall() # 获取所有数据
except:
print('表数据查询失败!')
return
# 关闭游标和连接
cur.close()
conn.close()
# 打印提示信息
print(f'{database}:{table_name}表数据查询完成!')
return data
删除database下table_name表中id为ids的数据
def delete_data(database, table_name, ids):
# 连接到数据库
try:
conn = psycopg2.connect(database=database, **db_connection) # 连接数据库
except:
print('数据库连接失败!')
return
# 创建一个游标对象
cur = conn.cursor()
# 执行删除数据的SQL语句
try:
ids_str = ', '.join(str(id) for id in ids)
delete_data_query = f"DELETE FROM {table_name} WHERE id IN ({ids_str});"
cur.execute(delete_data_query) # 执行SQL语句
except:
print('数据删除失败!')
return
# 提交事务
conn.commit()
# 关闭游标和连接
cur.close()
conn.close()
# 打印提示信息
print('数据删除成功!')
实验
import pandas as pd
import psycopg2
from psycopg2 import sql
from psycopg2.extensions import ISOLATION_LEVEL_AUTOCOMMIT
# 数据库连接参数
db_connection = {
"host": "localhost",
"port": "5432",
"user": "postgres",
"password": "your password",
}
columns = [
"id serial PRIMARY KEY",
"name varchar(100)",
"age int"
]
create_tb('weather', 'day4', columns)
df = pd.DataFrame({
"id": [1, 2, 3],
"name": ["张三", "李四", "王五"],
"age": [18, 19, 20]
})
insert_df('weather', 'day4', df)
tb_data('weather', 'day4')