postgresql相关常用操作及命令（python）---- 笔记

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