Python实现数据库中根据主键ID进行数据合并拼接

        在同一个数据库中，有两张表，主表主键为id，第二张表的主键为pid（id与pid为通用主键，只是在不同的表中可能命名不一样）。这里是以第二张表为中心，根据pid与主表中的id进行匹配，获取某个字段信息，例如身份证号，进行合并拼接，具体代码实现如下：

import psycopg2
import pandas as pd
import os
import json

def get_dataset():
    #读取库中数据
    conn = psycopg2.connect(database='******',user='******', password='******', host='******', port='******')
    cur = conn.cursor()
    cur.execute("SELECT pid,pname,xingming,xingbie_name,nianling,suoqizuoyong_name,panchu,huiji_name,pro_name FROM j_000035")
    data = cur.fetchall()
    keys = ["pid","pname","xingming","xingbie_name","nianling","suoqizuoyong_name","panchu","huiji_name","pro_name"]
    result = pd.DataFrame(data, columns=keys)
    result = result.rename(columns={'pid': 'id'})
    # print(result)
    # res = result.to_dict(orient='records')
    # print(res)

    cur.execute("SELECT id,shenfenzhenghao FROM j_000025 ")
    data1 = cur.fetchall()
    keys = ["id", "zhengjianhao"]
    result1 = pd.DataFrame(data1, columns=keys)
    # print(result1)
    newDf = pd.merge(result, result1,on= 'id')
    print(newDf)
    newDf.to_excel('表名.xlsx', sheet_name='关系表')

if __name__ == '__main__':
    get_dataset()

最后第二张表根据id获取到了证件号，如下所示：

通过SQL解决：

WITH  AS子句（根据id关联）：

WITH table1 AS
            (SELECT _id,name_,mediatype,justiceoffice FROM test_123 
            WHERE _typeid='daf3f452ae45a06784d71e1c1' 
            AND justicebureau='5423442df59b4e2bb1a1324a7e7d658a'),
            
            table2 AS (
SELECT _id,name_ FROM test456 WHERE 
_typeid='sd76983djaj09042bhad8832')

SELECT a.*,b.name_ as justiceoffice_display FROM table1 a 
LEFT JOIN table2 b ON a.justiceoffice = b._id

INNER JOIN 连接语句 

SELECT c.g_id,xingming,c.movein_address,
        to_char(create_time,'yyyy-mm-dd') launch_date,status_, zhengjianhao,
      (case when zhuanru='1' then '是' else '否' end) res, 
      anyou_name,leibie_name,jzjb_name FROM _jibenxx  a 
      INNER JOIN _person b ON a.l_id = b.l_id and sysid=1 
      INNER JOIN qianchushenqing c ON b.g_id = c.g_id  
      WHERE jiaoriqi is not null and zhongzhiriqi is null