Python MySQL query problems

ralvez · 2008-05-17 18:58:23

I have a relatively large project I'm working on and I have a problem that I cannot figure out (working on it for the last 3 days !!). I know in this forum there are some very skilled programmers so I thought to ask (although it is not Arch related).
OK. Here we go:
I created a database driven web services site. When I run the installation scripts, all works out fine and the database back end is created and basic default values are automatically assigned as expected.
Then I start the web server and the web site comes to "life" as expected too. There are a number of links that are fetched from the database using python (mod_python) and embedded procedures in the DB and those also are working fine.

Now, to the problem.
Once the site is up and running the administrator can change the default links in the database server so that the links become relevant to his/her organization. I have in the MySQL server an embedded procedure to handle this changes, here it is:

DELIMITER $$

DROP PROCEDURE IF EXISTS `addlink`$$
CREATE DEFINER=`ralvez`@`localhost` PROCEDURE `addlink`(strLink varchar(120),strTitle varchar(40),strSection varchar(40),intPosition int)
BEGIN
  -- Determine the currently last used position in the desired Section and store it in @last_position
  select MAX(position) from links where links.`section` = strSection into @last_position;    
  if (intPosition > 0)then  -- this means we are using manual positioning of the links
    if (intPosition = @last_position ) then   -- this link already exists, so do an update
        update links SET `link`= strLink, `title` = strTitle WHERE `position` = intPosition and `section` = strSection;
    else   -- the link is new so perform an insert of the type "Manual Positioning"
        insert into links (link,title,section,position) VALUES (strLink,strTitle,strSection,intPosition);
    end if;

  elseif (intPosition = 0) then   -- this means we are using Autopositioning of links (added after the last link)
    SET @last_position = @last_position + 1;    -- move up one from the last position and insert new link
    insert into links (link,title,section,position) VALUES (strLink,strTitle,strSection,@last_position);
  end if;
END$$

DELIMITER ;

If I execute the embedded procedure "by hand" at the mysql prompt it works as expected and adds (or updates if it finds that the link is already there) as expected so I know this script is correct; however when I send the command over the web via a python script it reports that it worked, I get no errors or warnings but nothing happens at the database level; that is to say, no links get added or updated.

This is the command I send with python:

 try:    ## TODO add better error handling here!!
                cursor = dbconn.active_connection.cursor()
                [b]cursor.execute("call addlink (%s,%s,%s,%s)",(link,title,section,int(position)))[/b]
            except MySQLdb.Error, error:
                print "Processing error:", error
                dbconn.exit_db()
                exit()

Does anyone here can see something "obvious" or subtle that I'm not doing or I'm doing wrong?
BTW I have tried to send the last argument as %d (since it is an integer in the database) but I get an error from MySQL indicating that "the integer parameter is required" so obviously that approach does not work.

I'm not looking for an answer like "do this and it will work" but if someone has it I'll take it ... but I'm looking for ideas to troubleshoot the problem.
The /var/log/httpd/error_log has no errors, the /var/log/mysqld has no errors either ... python provides no errors neither ...

I'm wondering: is there any way to trace whatever goes on the back end in MySQL to reveal queries sent over the network? that could be a way to know what the problem is ...
Any help, any idea, is welcome ...

Regards,

Rick

setsuna · 2008-05-17 20:47:04

Have you tried

callproc(procname, args)

It's a method of the cursor class.

Haven't tried it myself, though.

ralvez · 2008-05-17 21:21:23

@setsuna,

Thanks for your reply.
I did give it a try like this:

cursor.callproc ("addlink",(link,title,section,position))

And again, it looks like it worked but ... no links are updated.
This is very frustrating!! I'll bet money it is something very silly that is getting in the way.

noalwin · 2008-05-17 21:35:54

What about doing dbconn.active_connection.commit() after call the cursor?

or setting dbconn.active_connection.commit(True) before

ralvez · 2008-05-17 22:40:38

@noalwin,

Yes!! It works!!
Thank you, thank you, thank you!!
Now I remember that in another project I worked on (using python too) I had added the commit statement into the embedded procedure, so if the process succeeded it would commit and if it failed it would roll-back.
Fantastic!!.

Thank you and setsuna for your help. I knew I would fin the right answers here!

Rick

Arch Linux

#1 2008-05-17 18:58:23

Python MySQL query problems

#2 2008-05-17 20:47:04

Re: Python MySQL query problems

#3 2008-05-17 21:21:23

Re: Python MySQL query problems

#4 2008-05-17 21:35:54

Re: Python MySQL query problems

#5 2008-05-17 22:40:38

Re: Python MySQL query problems

Board footer