Creating big query in sqlalchemy

Question

So I'm fresh user of sqlalchemy and i want to create kinda big query. Here is my code

    def post2(self, semestr):
    nazwa_kierunku = 'stac. I st., kier. informatyka'
    nr_semestru = 2

    nr_grupy_cw = 3
    nr_grupy_ps = 5
    nr_grupy_l = 7
    nr_grupy_w = 1
    nr_grupy_p = 2
    nr_grupy_s = 5
    nr_grupy_j = 5
    nr_grupy_wf = 1

    prowadzacy_tytul_subq = model.Prowadzacy.query.join(model.Tytul).subquery()

    query = model.Grupa.query.join(prowadzacy_tytul_subq,
            model.Studia,
            model.Przedmiot,
            model.Sala).filter_by(model.Studia.nazwa == nazwa_kierunku, model.Grupa.nr_semestru == nr_semestru).filter(model.Grupa.rodzaj = 'Ćw', model.Grupa.nr_grupy == nr_grupy_cw).filter(model.Grupa.rodzaj = 'Ps', model.Grupa.nr_grupy == nr_grupy_ps).filter(model.Grupa.rodzaj = 'L', model.Grupa.nr_grupy == nr_grupy_l).filter(model.Grupa.rodzaj = 'W', model.Grupa.nr_grupy == nr_grupy_w).filter(model.Grupa.rodzaj = 'P', model.Grupa.nr_grupy == nr_grupy_p).filter(model.Grupa.rodzaj = 'S', model.Grupa.nr_grupy == nr_grupy_s).filter(model.Grupa.rodzaj = 'J', model.Grupa.nr_grupy == nr_grupy_j).filter(model.Grupa.rodzaj = 'Ćw', model.Grupa.nr_grupy == nr_grupy_wf).order_by(
                model.Grupa.dzien_tyg,
                model.Grupa.godz_rozp)

    result = schema.GrupaSchema().dump(query, many=True)

    return jsonify(result)

The error i get is "can't assign to function call" when i mouseover my query and SyntaxError: keyword can't be an expression while i try to compile it. I don't know what I can change in this code to make it work ;/

P.S. Ok I read some stuff and now my code looks like this

    def post(self, semestr):
    nazwa_kierunku = 'stac. I st., kier. informatyka'
    nr_semestru = 2

    nr_grupy_cw = 3
    nr_grupy_ps = 5
    nr_grupy_l = 7
    nr_grupy_w = 1
    nr_grupy_p = 2
    nr_grupy_s = 5
    nr_grupy_j = 5
    nr_grupy_wf = 1

    prowadzacy_tytul_subq = model.Prowadzacy.query.join(model.Tytul).subquery()

    query = (
        model.Grupa.query.join(prowadzacy_tytul_subq,
                               model.Studia,
                               model.Przedmiot,
                               model.Sala)
            .filter(model.Studia.nazwa == nazwa_kierunku)
            .filter(model.Grupa.nr_semestru == nr_semestru)
            .filter(or_(model.Grupa.rodzaj == 'Ćw', model.Grupa.nr_grupy == nr_grupy_cw))
            .filter(or_(model.Grupa.rodzaj == 'Ps', model.Grupa.nr_grupy == nr_grupy_ps))
            # .filter(or_(model.Grupa.rodzaj == 'L', model.Grupa.nr_grupy == nr_grupy_l))
            #.filter(or_(model.Grupa.rodzaj == 'W', model.Grupa.nr_grupy == nr_grupy_w))
            # .filter(or_(model.Grupa.rodzaj == 'P', model.Grupa.nr_grupy == nr_grupy_p))
            # .filter(or_(model.Grupa.rodzaj == 'S', model.Grupa.nr_grupy == nr_grupy_s))
            #.filter(or_(model.Grupa.rodzaj == 'J', model.Grupa.nr_grupy == nr_grupy_j))
            # .filter(or_(model.Grupa.rodzaj == 'wf', model.Grupa.nr_grupy == nr_grupy_wf))
            # .order_by(model.Grupa.dzien_tyg, model.Grupa.godz_rozp)
    )

    result = schema.GrupaSchema().dump(query, many=True)

    return jsonify(result)

The problem is with result. When I use it as you see the result is right. When i uncomment the next line (or any next) which has 'or' suddenly the result is empty. Don't really understand why it's happening that way. Any idea?

This question gives too many specific details (basically a copy and paste job) and is not reproducible. Please try condensing the question into the relevant parts so 1. People can easily understand it and 2. People can apply the solution to their own issues — cd98, Oct 08 '18 at 02:54

kennytm · Answer 1 · 2017-03-12T06:03:17.640

.filter(model.Grupa.rodzaj = 'Ćw',
#                          ^

Perhaps you should be using == here?

BTW your .filter line is too long. Consider split it up into multiple lines:

# Note: The code is modified only for syntax, it does not mean the query works correctly.
query = (
    model.Grupa.query.join(prowadzacy_tytul_subq, 
        model.Studia, 
        model.Przedmiot, 
        model.Sala)
    .filter(model.Studia.nazwa == nazwa_kierunku, model.Grupa.nr_semestru == nr_semestru)
    .filter(model.Grupa.rodzaj == 'Ćw', model.Grupa.nr_grupy == nr_grupy_cw)
    .filter(model.Grupa.rodzaj == 'Ps', model.Grupa.nr_grupy == nr_grupy_ps)
    .filter(model.Grupa.rodzaj == 'L', model.Grupa.nr_grupy == nr_grupy_l)
    .filter(model.Grupa.rodzaj == 'W', model.Grupa.nr_grupy == nr_grupy_w)
    .filter(model.Grupa.rodzaj == 'P', model.Grupa.nr_grupy == nr_grupy_p)
    .filter(model.Grupa.rodzaj == 'S', model.Grupa.nr_grupy == nr_grupy_s)
    .filter(model.Grupa.rodzaj == 'J', model.Grupa.nr_grupy == nr_grupy_j)
    .filter(model.Grupa.rodzaj == 'Ćw', model.Grupa.nr_grupy == nr_grupy_wf)   
#                                  ^^ should be 'Wf' here?
    .order_by(model.Grupa.dzien_tyg, model.Grupa.godz_rozp)
)

compilator says .filter_by(model.Studia.nazwa == nazwa_kierunku, model.Grupa.nr_semestru == nr_semestru) that in this line was TypeError: filter_by() takes 1 positional argument but 3 were given. Also there should be 'wf' and thanks for notice :> — Wesspe, Mar 11 '17 at 22:21
I decided to split this filter_by into .filter_by(model.Studia.nazwa == nazwa_kierunku) .filter(model.Grupa.nr_semestru == nr_semestru) still compilator says filter_by() takes 1 positional argument but 2 were given. Why this filter_by is not working? — Wesspe, Mar 11 '17 at 22:30
@Wesspe looks like you need to use `filter` instead. `filter_by` expects [kwargs](http://stackoverflow.com/questions/1769403/understanding-kwargs-in-python). — kennytm, Mar 11 '17 at 22:51

Creating big query in sqlalchemy

1 Answers1