postgresql单条和多条数据的增、删、改、查，以及其他一些用法

1、增
1.1单个插入
sql = f"""insert into {table_name}(id, gid, projectid, pipelength, source, target, x_source, y_source,
         x_target, y_target, geom) VALUES ({data['id']},{data['gid']},'{data['projectid']}',{data['pipelength']},
          {data['source']}, {data['target']},{data['x_source']}, {data['y_source']},{data['x_target']},
            {data['y_target']},ST_GeomFromText('{data['geom']}', 3857))"""

1.2批量插入
sql = f"""insert into dt_node_test {'({})'.format(', '.join(df.columns))} values %s """

1.3原生sql批量插入(注意在values中有字符串类型的要加引号，如：'{dic['projectid']}')
    def geometry_transform(self):
        new_node_info_list = [
            {'gid': 1,'projectid': 123,  'geom': f"LINESTRING(13199980.3772 13199980.3772, 13199980.3772 13199980.3772)"},
            {'gid': 2, 'projectid': 123, 'geom': f"LINESTRING(13199980.3772 13199980.3772, 13199980.3772 13199980.3772)"}]
        pipe_data = pd.DataFrame(new_node_info_list)
        print(pipe_data)
        pipe_columns_str = '({})'.format(', '.join(pipe_data.columns))
        pipe_data_dict = pipe_data.to_dict(orient='records')
        col_str = ''
        for dic in pipe_data_dict:
            c = f"""({dic['gid']},'{dic['projectid']}',ST_GeomFromText('{dic['geom']}', 3857)),"""
            col_str += c
        print(col_str[:-1])
        col_str = "INSERT INTO dt_pipeline_test {} VALUES ".format(pipe_columns_str) + col_str[:-1]
        print(col_str)
        self.insert(col_str)
        self.close()


2、删
sql = f"""delete from {table_name} where possession in ('市政', '庭院') or pressurerating = '低' and 
        projectid = '{project_id}'"""


3、改
3.1单条更新
sql = f"""update {table_name} set x_source={data['x_source']}, y_source={data['y_source']},
        x_target={data['x_target']},y_target={data['y_target']},geom=ST_GeomFromText('{data['geom']}', 3857) where
         projectid='{project_id}' and id={data['id']}"""

3.2批量更新
s = ''
        for i in columns:
            s += f'{i} = new_data.{i},'
        sql = f"""update {table_name} set {s[:-1]}  from (values %s) as new_data ({','.join(columns)}) where projectid='{project_id}';"""

3.3原生sql批量更新
			pipe_data_dict = pipe_data.to_dict(orient='records')
            pipe_col_str = ''
            for dic in pipe_data_dict:
                c = f"""({dic['id']},{dic['x_source']},{dic['y_source']},{dic['x_target']},{dic['y_target']},
                                        ST_GeomFromText('{dic['geom']}', 3857)),"""
                pipe_col_str += c
            pipe_sql_batch = f"""update dt_pipeline_test set x_source=new_data.x_source, y_source=new_data.y_source,
            x_target=new_data.x_target, y_target=new_data.y_target, geom=new_data.geom from (values {pipe_col_str[:-1]}) 
            as new_data (id, x_source, y_source, x_target, y_target, geom) where dt_pipeline_test.id=new_data.id and
            projectid = '{self.project_id}'"""
            self.update(pipe_sql_batch)

            self.close()


4、查
sql = f"""select id, ST_AsText(geom) from {table_name} where projectid='{projectid}' """```

# 将带有geom字段的df，批量插入
def insert_pg(self, data_df):
    """将保存结果插入数据库"""
    node_data_dict_insert = data_df.to_dict(orient='records')
    node_columns_str = '({})'.format(', '.join(data_df.columns))
    node_col_str2 = ''
    for dic in node_data_dict_insert:
        c = f"""({dic['gid']},'{dic['projectid']}',{dic['source']},{dic['target']},
        {dic['x_source']},{dic['y_source']},{dic['x_target']},{dic['y_target']},{dic['delete_status']},
        ST_GeomFromText('{dic['geom']}', 3857)),"""
        node_col_str2 += c
    node_sql_batch2 = "INSERT INTO dt_node_test {} VALUES ".format(node_columns_str) + node_col_str2[:-1]
    self.pg_op.insert(node_sql_batch2)

    pipe_data_dict = self.pipes_df.to_dict(orient='records')
    pipe_col_str = ''
    for dic in pipe_data_dict:
        c = f"""({dic['id']},{dic['gid']},{dic['source']},{dic['target']},{dic['x_source']},{dic['y_source']},
                {dic['x_target']},{dic['y_target']},ST_GeomFromText('{dic['geom']}', 3857)),"""
        pipe_col_str += c
    pipe_sql_batch = f"""update dt_pipeline_test set gid=new_data.gid, source=new_data.source, target=new_data.target,
                 x_source=new_data.x_source, y_source=new_data.y_source, x_target=new_data.x_target, y_target=new_data.y_target,
                  geom=new_data.geom from (values {pipe_col_str[:-1]}) as new_data (id, gid, source, target, x_source, y_source,
                   x_target, y_target, geom) where dt_pipeline_test.id=new_data.id and projectid = '{self.project_id}'"""
    self.pg.update(pipe_sql_batch)

    node_data_dict_insert = self.nodes_df2.to_dict(orient='records')
    node_columns_str = '({})'.format(', '.join(self.nodes_df2.columns))
    node_col_str2 = ''
    for dic in node_data_dict_insert:
        c = f"""({dic['gid']},'{dic['projectid']}',{dic['xcoordinate']},{dic['ycoordinate']},'{dic['type']}',
                    ST_GeomFromText('{dic['geom']}', 3857)),"""
        node_col_str2 += c
    node_sql_batch2 = "INSERT INTO dt_node_test {} VALUES ".format(node_columns_str) + node_col_str2[:-1]
    self.pg.insert(node_sql_batch2)

    self.pg.close()


import pandas as pd


def insert_pg_new(data_df):
    """带geom的df批量插入"""
    node_data_dict_insert = data_df.to_dict(orient='records')
    print(node_data_dict_insert)
    node_columns_str = '({})'.format(', '.join(data_df.columns))
    s = "("
    for i in range(data_df.shape[0]):
        dic = node_data_dict_insert[i]
        s += "("
        for col in data_df.columns:
            if col in ['geom', 'geometry']:
                s += f"""ST_GeomFromText('{dic[col]}', 3857),"""
            elif isinstance(dic[col], str):
                s += f"""'{dic[col]}',"""
            else:
                s += f"""{dic[col]},"""
        s = s[:-1]
        s += "),"

    s += ")"
    s = s[:-2]
    s += ")"

    node_sql_batch = "INSERT INTO dt_node_test {} VALUES ".format(node_columns_str) + s
    print(node_sql_batch)
    # self.pg_op.insert(node_sql_batch)


df = pd.DataFrame({"a": [1, 2], "geom": ["116.327603,39.89962", "12,13"], "address": ['a ', " b"]})
print(df)
df['address'] = df['address'].str.strip()
df[['x', 'y']] = df['geom'].str.split(",", expand=True)
df['geom'] = df[['x', 'y']].apply(lambda z: f'POINT({z["x"]} {z["y"]})', axis=1)
print(df)
insert_pg_new(df)


def insert_pg_new(self, data_df):
    """带geom的df批量插入"""
    node_data_dict_insert = data_df.to_dict(orient='records')
    node_columns_str = '({})'.format(', '.join(data_df.columns))
    s = "("
    for i in range(data_df.shape[0]):
        dic = node_data_dict_insert[i]
        s += "("
        for col in data_df.columns:
            if col in ['projectid', "name", "address", "assign_type", "client_code"]:
                s += f"""'{dic[col]}',"""
            elif col in ['geom', 'geometry']:
                s += f"""ST_GeomFromText('{dic[col]}', 3857),"""
            else:
                s += f"""{dic[col]},"""
        s = s[:-1]
        s += "),"

    s += ")"
    s = s[:-2]
    s += ")"

    node_sql_batch = "INSERT INTO dt_node_test {} VALUES ".format(node_columns_str) + s
    self.pg_op.insert(node_sql_batch)

# 赋值一张表的数据，并将新表重命名
CREATE TABLE dt_node_sr_test AS SELECT * FROM dt_node_sr;

# 插入一条数据
insert into project_map_manage (id, map_name, description, created_time, apply, delete_status) VALUES (1, '地图1','描述1', '2023-10-20 15:30:00',1,0);

# 查询数据
select apply from project_map_manage where id=1

# 更新数据
update project_map_manage set apply=1 where id=1

# 删除数据
delete from project_map_manage where id=1

# pg库查询某个字段不重复的内容
SELECT DISTINCT projectid FROM dt_pipeline_test;

# 通过一条 SQL 语句自动获取当前中国时间并将其插入 PostgreSQL 数据库
INSERT INTO your_table (timestamp_column) VALUES (current_timestamp AT TIME ZONE 'Asia/Shanghai');

# 将列表中某列设置为自增长
1、先创建一个从1自增长的序列：CREATE SEQUENCE bak_version_id_seq START 1; 或者：CREATE SEQUENCE bak_version_id_seq
2、然后在在设计表中将该列的默认填为：nextval('bak_version_id_seq'::regclass)

# 查询点表和线表中的任意10条数据，并显示坐标里面的内容
SELECT *, ST_AsText(geom) FROM point_table LIMIT 10;
SELECT *, ST_AsText(geom) FROM line_table LIMIT 10;

# 查找包含某些gid的数据
SELECT * FROM line_table WHERE gid in (38176, 38178, 38179) AND projectid = 'qz_mid_add';

# 更新单条geom坐标
update dt_pipeline set geom=ST_GeomFromText('MULTILINESTRING((13375855.14248983 4291427.794671041,13375869.3243 4291382.4201,13375881.9408 4291342.2555, 13375813.7012 4291577.0993,13375828.2321 4291526.9876,13375842.4657 4291476.83010001,13375855.142489837 4291427.794671041),(13375855.142489837 4291427.794671041,13375858.7715 4291413.75720001,13375854.3206 4291430.42430001,13375855.142489837 4291427.794671041))', 3857) where project_code='dt_qd_hs'

# 查找geom的数据
SELECT *,ST_AsText(geom) FROM dt_pipeline_test WHERE projectid = 'dt_qd_hs';

# 复制一张新表，并将其id自增长关联过来
 sql_pipe = f"""
 DROP TABLE IF EXISTS {GIS_MAP_DT_PIPE};
 -- 创建新表并复制数据
 CREATE TABLE {GIS_MAP_DT_PIPE} AS SELECT * FROM {self.source_table};

 -- 删除序列
 DROP SEQUENCE IF EXISTS {GIS_MAP_DT_PIPE}_id_seq;
 CREATE SEQUENCE {GIS_MAP_DT_PIPE}_id_seq;

 -- 将序列与 id 列关联
 ALTER TABLE {GIS_MAP_DT_PIPE} ALTER COLUMN id SET DEFAULT nextval('{GIS_MAP_DT_PIPE}_id_seq');
 -- 设置 id 列为主键
 ALTER TABLE {GIS_MAP_DT_PIPE} ADD PRIMARY KEY (id);
 SELECT setval('{GIS_MAP_DT_PIPE}_id_seq', (SELECT max(id) FROM {GIS_MAP_DT_PIPE}));

# 统计字段为projectid为qd_mid1_v_1_1有多少条数据
SELECT count(1) FROM dt_node_test WHERE projectid='qd_mid1_v_1_1'

# 删除表中数据，连同id自增也删除
truncate dt_node_test;

给projectid设置索引