将MySQL查询转换为SQLalchemy查询

Question

我试图从我的服务器上移植所有原始的SQL脚本，这些脚本使用一个简单的BASH脚本来运行，这样我就可以用python和sqlalchemy来处理数据，但是下面这一个脚本拒绝移植。我也在下面列出了我最接近的复制它的尝试。在

如果有人知道我错在哪里，或者你能复制它，请随意。在

还要注意，有些表已经用填充名替换了bean。在

-----MYSQL-----

SELECT *
FROM
(SELECT CONCAT('{URL}', TBL1.ColumID) AS 'Link',
    TBL1.ColumID AS CaseID,
    TBL1.ColumType AS 'Abuse Type',
    TBL1.Status AS 'Status',
    TBL1.Role AS 'Queue', TBL1.CreationDate, TBL1.LastUpdated, @LAST_TOUCHED:=
    (SELECT from_unixtime((max(unix_timestamp(LAST_UPDATED))))
        FROM TBL2
        WHERE TBL1.ColumID = CaseID
        AND (NOTE_TYPE = 'communication'
            OR NOTE_SUBTYPE IS NULL)) AS last_touched, ((unix_timestamp(now()) - unix_timestamp(LAST_UPDATED))/3600/24) AS Age,
    CASE
    WHEN isnull(@LAST_TOUCHED) THEN 'N'
    ELSE 'Y'
    END AS Touched
    FROM TBL1
    WHERE TBL1.ROLE REGEXP '{ROLE Filler}'
    AND STATUS REGEXP 'RESOLVED|DELETED' =0) AS tbl
WHERE tbl.Age > 2
order by tbl.Age desc

------失败的Python代码-----

^{pr2}$

Answer 1

首先，再次确认你真的必须这么做。您这样做是为了在Web服务器中包含查询，还是提取数据进行分析？如果是后者，只需使用pandas：

import pandas as pd
import sqlalchemy as sa

engine = sa.create_engine('{connection string}')
df = pd.read_sql_query("{insert your complicated query here}", engine)