postgresql常用操作及命令(python)---- 笔记
一、数据库命令(常用)
-- 创建新表
CREATE TABLE table_name(
id SERIAL PRIMARY KEY, -- 连续唯一ID
stock_code VARCHAR(100) NOT NULL, -- 可变字符长度为100
db_name VARCHAR(100) NOT NULL,
index double precision NOT NULL, -- 双精度数据
data_date date NOT NULL, -- 日期,只有年月日
created_date TIMESTAMP NOT NULL default CURRENT_TIMESTAMP, -- 日期,精确到秒,即年月日时分秒
updated_date TIMESTAMP NOT NULL default CURRENT_TIMESTAMP
);
-- 添加新字段
ALTER TABLE table_name ADD COLUMN data_date TIMESTAMP;
-- 修改已存在的表字段
ALTER TABLE table_name ALTER COLUMN data_date TYPE TIMESTAMP;
-- 关闭数据库连接
SELECT pg_terminate_backend(pg_stat_activity.pid)
FROM pg_stat_activity WHERE datname='grid9' AND pid<>pg_backend_pid();
-- 修改数据库名
alter database old_databasename rename to new_databasename;
-- 查看数据库连接
select pg_terminate_backend(pid) from (select pid from pg_stat_activity where datname = 'database_name' ) a;
-- 关闭数据库
SELECT pg_terminate_backend(pg_stat_activity.pid)
FROM pg_stat_activity WHERE datname='database_name' AND pid<>pg_backend_pid();
-- 修改数据库名字
alter database old_database_name rename to new_database_name;
-- 添加字段注释
TABLESPACE pg_default;
ALTER TABLE IF EXISTS library_name.table_name
OWNER to postgres;
COMMENT ON TABLE library_name.table_name
IS '表含义';
COMMENT ON COLUMN library_name.table_name.field_nname
IS '字段含有';
-- 查询所有表数据量(非系统)
select relname as TABLE_NAME, reltuples as rowCounts from pg_class
where relkind = 'r' and relnamespace = (select oid from pg_namespace where nspname='stock_data') order by rowCounts desc;
-- 删除限定条件数据
DELETE FROM stock_data.security_basic_info
WHERE trade_date!='2021-03-01';
-- timetamp时间模糊查询
select * from stock_data.minute_quotation where (TO_CHAR("created_date", 'yyyy-MM-dd hh24:mi:ss') LIKE '2022-03-09%%');
-- timetamp时间模糊删除
DELETE from stock_data.minute_quotation where (TO_CHAR("created_date", 'yyyy-MM-dd hh24:mi:ss') LIKE '2022-03-09%%');
二、数据库连接
数据表已存在
import psycopg2
conn = psycopg2.connect(database='postgres', user='postgres', password='postgres', host='localhost', port='5432')
cursor = conn.cursor()
# 执行sql
cursor.execute(sql)
# 提交执行结果
conn.commit()
# 关闭游标
# cursor.close()
#关闭数据库连接
# conn.close()
数据表不存在,自动创建
from sqlalchemy import create_engine
engine = create_engine('postgresql://user:password@localhost:5432/database')
to_sql方式
# if_exists判断表存在如何操作,默认为'fail':表存在引发ValueError;replace:在插入新值之前删除表;append:将值插入现有表。
index_data.to_sql(table_name, engine, index=False, if_exists='append')
copy_expert方式
import io
# 以字符串的方式读取数据
string_data_io = io.StringIO()
# 按照 ‘|’分隔数据,并去掉索引
df.to_csv(string_data_io, sep='|', index=False)
# pandas连接数据表
pd_sql_engine = pd.io.sql.pandasSQL_builder(engine)
# 创建表
table = pd.io.sql.SQLTable(table_name, pd_sql_engine, frame=df, index=False, if_exists=if_exists)
table.create()
# 0表示从头开始找数据
string_data_io.seek(0)
# 去除表头的dataframe数据
string_data_io.readline()
with engine.connect() as connection:
with connection.connection.cursor() as cursor:
copy_cmd = "COPY %s FROM STDIN HEADER DELIMITER '|' CSV" %table_name
cursor.copy_expert(copy_cmd, string_data_io)
connection.connection.commit()
在同等数据同等情况下,copy_from执行效率最高,其次是copy_expert,最低的效率是to_sql
copy_expert执行花费时间: 0.22238731384277344
to_sql执行花费时间: 0.9240171909332275
copy_from执行花费时间: 0.0919795036315918