How to properly select duplicate field names in a raw JOIN query in SQLAlchemy

Question

I am tying to run a raw MySQL JOIN query using Connection.execute() in SQLAlchcemy. However, I am having trouble with getting the data of any two fields/columns with the same name.

My models:

class Client(db.Model):
    id = db.Column(db.Integer, primary_key=True, autoincrement= True)
    client_name = db.Column(db.String(80), nullable=False)
    email = db.Column(db.String(120), nullable=False)

class User(db.Model):
    __table_args__ = {'mysql_engine': 'InnoDB', 'mysql_charset': 'utf8', 'mysql_collate': 'utf8_general_ci'}
    id = db.Column(db.Integer, primary_key=True, autoincrement=True)
    username = db.Column(db.String(80), nullable=False)
    email = db.Column(db.String(120), nullable=False)
    client_id = db.Column('client_id', db.Integer, nullable=True, index=True)

As you can see in both models I have a field called id

Then I have the function

app = Flask(__name__)
app.config['SQLALCHEMY_DATABASE_URI'] = 'mysql+pymysql://my_user:password@localhost/my_db'
db = SQLAlchemy(app)
conn = db.engine.connect()
sql_statement = """SELECT * FROM User u JOIN Client c ON u.client_id = c.id"""
info = conn.execute(sql_statement)
for row in info:
   print(row)
   row = dict(row)
   print(row)

When I loop through the results of the execution I expect to get back records with two fields u.id and c.id however I am getting only one field with the name id (not even u.id or c.id).

I have tried both SELECT u.*, c.* and SELECT u.id , c.id but that did not work.

The first print (before converting to dict) prints the values of all the fields with both ids, but after I convert it to dict it does not work anymore, and in my use-case, I must convert it to dict.

How can I get the expected behaviour without having to use AS in my query?

Note: If I use SELECT u.* AS u_id, c.id AS c_id it works fine but I am looking for another way without using AS.

While not really an answer you're looking for, in general the use of `*` in anything but interactive one-time queries is a bit frowned upon, as it ties your implementation to the layout of the table. — Ilja Everilä, Nov 18 '19 at 09:42

score 0 · Answer 1 · answered Nov 18 '19 at 08:36

The only work around I found for this issue was to do the following:

sql_statement = """SELECT u.id AS `u.id`, c.id AS `c.id` FROM User u JOIN Client c ON u.client_id = c.id"""

This is not a solution for this issue but just the work around I followed and still not so satisfied with!

How to properly select duplicate field names in a raw JOIN query in SQLAlchemy

1 Answers1