how to select elements by choose parameters from another table in SQLite3 python

Question

Is it possible to return city list (for example) from Poland (country_id = 76) but using country name (value of country.country row) in WHERE not directly country_id? The structure of database bellow.

@app.route('/citiess')
def city_list3():
    db = get_db()
    data = db.execute('''
        SELECT city FROM city 
        JOIN country ON city.country_id = country.country_id
        WHERE city.country_id = 76
        ''').fetchall()
    data_json = []
    for i in data:
        data_json.extend(list(i))

    return jsonify(data_json)

zwer · Accepted Answer · 2018-04-02 17:53:56Z

1

Why don't you just do your WHERE clause on the joined country table, i.e.:

data = db.execute("""SELECT city FROM city
                     JOIN country USING (country_id)
                     WHERE country.country = ?""", ("Poland", )).fetchall()

edited Apr 2, 2018 at 17:53

answered Apr 2, 2018 at 17:49

zwer

25.9k3 gold badges53 silver badges70 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

derriadiergummi · Accepted Answer · 2018-04-02 17:57:13Z

0

I see that also works:

data = db.execute('''
    SELECT city FROM city 
    JOIN country ON country.country_id = city.country_id
    WHERE country.country = "Poland"
    ''').fetchall()

anyway thanks

answered Apr 2, 2018 at 17:57

derriadiergummi

631 silver badge7 bronze badges

1 Comment

zwer Over a year ago

I just parameterized it so you can safely reuse your query for any country you want instead of changing the SQL directly.

Collectives™ on Stack Overflow

how to select elements by choose parameters from another table in SQLite3 python

2 Answers 2

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related