CODEWITHSUNDEEP
pythonsqlite
Joe Flip
Joe Flip

Reputation: 1205

sqlite3: Preserve table names in query with JOIN

I want to execute a SQL query with a JOIN where I can propagate the table aliases into the resulting dictionary keys. For example, I may have a query

query = """
        SELECT t1.col1,t2.col1,t2.col2 
        FROM table1 t1 JOIN table2 t2
        ON t1.col0=t2.col0
        """

and I want the output to maintain the t1, t2 aliases, since I have duplicate column names (col1). I would run

con = sqlite3.connect(dbpath, isolation_level=None, detect_types=sqlite3.PARSE_DECLTYPES)

def dict_factory(cursor, row):
    d = {}
    for idx,col in enumerate(cursor.description): d[col[0]] = row[idx]
    return d

db.dict = con.cursor()
db.dict.row_factory = dict_factory
result = db.dict.execute(query).fetchone()

But this overwrites the col1 value. How can I have it return, say,

{'t1.col1':123, 't2.col1':234, 't2.col2':345}

Thanks!

Upvotes: 1

Views: 182

Answers (1)

CL.
CL.

Reputation: 180172

The documentation says:

The name of a result column is the value of the "AS" clause for that column, if there is an AS clause. If there is no AS clause then the name of the column is unspecified and may change from one release of SQLite to the next.

So you have to do:

SELECT t1.col1 AS "t1.col1", t2.col1 AS "t2.col1", ...

Upvotes: 1

Related Questions