在同一个数据库中,有两张表,主表主键为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