Why doesn't this sql script execute?

Question

Hi I have the following python:

c = conn.cursor()

#get the account id for the specific user

actidSQL = "select id from accounts where user_id = (select id from auth_user where username = '%s');" % user
c.execute(actidSQL)
actid = c.fetchone()[0]
print actid

#fill the latencies table - currently not firing, not sure why?
latencies_sql = "insert into latencies(account, replier, sender, replyemail, origemail, replydate, origdate) select m1.account, c1.id as replier, c2.id as sender, m1.id as replyemail, m2.id as origemail, m1.date as replydate, m2.date as origdate from contacts c1, contacts c2, emails m1, emails m2 where m1.id > m2.id and m1.reply = m2.mid and m1.reply is not null and c1.id = m1.fr and c2.id = m2.fr and m1.account = %s and m1.account = m2.account;" % (actid)
print latencies_sql
c.execute(latencies_sql)

The first sql executes but the second doesn't. Is there a reason why?

Are you sure your "select m1.account..." returns anything at all? — mojuba
– mojuba, Commented Sep 9, 2011 at 6:47
i print out the sql query and then run it on the database directly and it works. — locoboy
– locoboy, Commented Sep 9, 2011 at 6:49
Your queries are subject to injection. Better use c.execute(actidSQL, user) resp. c.execute(actidSQL, actid) and leave the %s in the query, without % operator. (I assume it is MySQL, other DBs might require a ? instead of a %s.) — glglgl
– glglgl, Commented Sep 9, 2011 at 6:53

Ocaso Protal · Accepted Answer · 2011-09-09 06:58:05Z

1

What do you mean with "The first sql executes but the second doesn't."? You get an error? Or is there no data in the DB? I assume that there is no data in the database and you are using MySQL. This is because you don't commit your changes. A conn.commit() at the end of your script should help.

edited Sep 9, 2011 at 6:58

answered Sep 9, 2011 at 6:51

Ocaso Protal

20.5k8 gold badges80 silver badges92 bronze badges

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

1 Comment

locoboy Over a year ago

bingo. i think this the issue.

Joe · Accepted Answer · 2011-09-09 06:46:58Z

0

It looks like an invalid query problem. You're trying to run either 2 queries, or 1.5 queries in the same c.execute

You either want an insert

insert into latencies(account, replier, sender, replyemail, origemail, replydate, origdate)

Or a select

select m1.account, c1.id as replier, c2.id as sender, m1.id as replyemail, m2.id as origemail, m1.date as replydate, m2.date as origdate from contacts c1, contacts c2, emails m1, emails m2 where m1.id > m2.id and m1.reply = m2.mid and m1.reply is not null and c1.id = m1.fr and c2.id = m2.fr and m1.account = %s and m1.account = m2.account;"

answered Sep 9, 2011 at 6:46

Joe

15.8k4 gold badges52 silver badges84 bronze badges

4 Comments

locoboy Over a year ago

so when i print this out to the console and then manually run it just as is, it seems to work fine.

glglgl Over a year ago

@Joe No. He wants to insert the result from a query into a table. This normally should work perfectly.

Piotr Auguscik Over a year ago

Insert values from select is valid query (at least for sql server)

Joe Over a year ago

Ah. I wasn't aware that syntax was valid, I've always used something a bit more verbose, and it looked weird :)

Collectives™ on Stack Overflow

Why doesn't this sql script execute?

2 Answers 2

1 Comment

4 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

4 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related