why the same functions with same querys gives a result while another not in sqlite?

Question

I don't get it ....

i have a few functions that return rows from sqlite tables. Problem is that some sql querys are working while another not. Function that working perfect :

    def selectAll(predstavnik_id):
        q = "SELECT * FROM ulaz WHERE predstavnik_id = (?)"
        cursor.execute(q, str(predstavnik_id))
        rows = cursor.fetchall()
        if rows is None:
            return None
        else:
            return rows

And the other one that don't return rows:

    def selectStanByUlaz(stan_id):
        q = "SELECT * FROM stan WHERE ulaz_id = (?)"
        cursor.execute(q, str(stan_id))
        rows = cursor.fetchall()
        if rows is None:
            return None
        else:
            return rows

There is no any errors, just won't return rows ! I tried in sqlite studio with same query syntax and it's working just fine What could be a problem ?

Thanks in advance

Of course that are not the same queries. First one querying table "ulaz" and another one querying table "stan". But the principle are the same ... — zlocko
– zlocko, Commented Jan 1, 2024 at 17:23

user2722968 · Accepted Answer · 2024-01-01 13:59:04Z

2

Both of these functions don't do what you expect them to do:

cursor.execute(q, str(predstavnik_id))
cursor.execute(q, str(stan_id))

The second argument to cursor.execute is defined to take a sequence of parameters.

Notice that cursor.execute(q, str(predstavnik_id)) is not executing with str(predstavnik_id) being the only parameter: cursor.execute is defined to take a sequence, and str(predstavnik_id) is a sequence of characters. You probably meant cursor.execute(q, (str(predstavnik_id), )) (notice the tuple), where str(predstavnik_id) is the only element of that sequence.

The other function suffers from the same problem.

Python should raise a RuntimeError if those strings are not exactly 1 character, yet if your id values are small integers (< 10), it will Just Work (tm) without you noticing the bug.

answered Jan 1, 2024 at 13:59

user2722968

17.4k5 gold badges58 silver badges77 bronze badges

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

4 Comments

Matthias Over a year ago

Good catch. Additionally: you don't even need the call to str.

user2722968 Over a year ago

@Matthias, yes, or maybe no. The code in the question only allures to the fact that the parameters are integers. Also, converting to a string before passing the object to execute forces the parameter-binding to not use any other converter. So it's not the same, and safer to just replicate what Op wrote, in order not to introduce subtle changes in behavior.

zlocko Over a year ago

Ok thanks, i'll try your solutions, but as i said, the first one working ok and returning rows from table "ulaz". The second one is the problem, it does'nt raise the error but just return None []. empty ...

user2722968 Over a year ago

@zlocko, both functions as shown in the question are wrong in any case. As I said above, if predstavnik_id is a small integer, it will seem to work, because str(5) is the same as ('5', ) when interpreted as a sequence.

zlocko · Accepted Answer · 2024-01-01 21:10:33Z

0

Sorry guys but it's nothing wrong with code. It's all in my idiotic head.

I put some dummy data in db for testing, but i put it in wrong version. Instead of db in my project, i referenced db (same name) in sqlite studio from older version of my project ... :-) Sorry for wasting your (&my) time

Happy new 2024

answered Jan 1, 2024 at 21:10

zlocko

71 bronze badge

Collectives™ on Stack Overflow

why the same functions with same querys gives a result while another not in sqlite?

2 Answers 2

4 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related