sqlalchemy lef join with order

Question

我正在尝试在sqlalchemy中进行此SQL查询:

SELECT t1.superior_id from "user" as t1 
LEFT JOIN "user" as t2 ON t1.superior_id = t2.id 
ORDER BY t2.first_name, t2.last_name;

整个事情是-以上级的名义订购用户。但仍然得到很多错误(取决于我现在尝试什么)。完全不知道如何在SA中复制这个查询。有人能帮忙吗?

Answer 1

我使用Oracle_11ginstantclient_21_3:https://docs.oracle.com/en/database/oracle/oracle-database/21/lacli/install-instant-client-using-zip.html

对于查询本身:

1. 我不会使用;在查询结束
2. 我不会使用";查询
中表的名称

import cx_Oracle
from sqlalchemy.engine import create_engine
import pandas as pd
try:
#im using Oracle_11ginstantclient_21_3 path as ORACLE_LIB_DIR  
cx_Oracle.init_oracle_client(lib_dir=ORACLE_LIB_DIR)
except cx_Oracle.Error as error:
print(error)
try:    
conn = create_engine("oracle+cx_oracle://" + USER + ":"+ PASS + "@" + DSN, max_identifier_length=30)
except cx_Oracle.Error as error:
print(error)
statement ="""
SELECT t1.superior_id from user as t1 
LEFT JOIN user as t2 ON t1.superior_id = t2.id 
ORDER BY t2.first_name, t2.last_name"""
df = pd.read_sql_query(statement, conn)