Create function through MySQLdb, createmysqldb
Http://stackoverflow.com/questions/745538/create-function-through-mysqldb How can I define a multi-statement function or procedure in using the MySQLdb lib in python? Example: import MySQLdbdb = MySQLdb.connect(db='service')c = db.cursor()c.execute("""DELIMITER //CREATE FUNCTION trivial_func (radius float) RETURNS FLOAT BEGIN IF radius > 1 THEN RETURN 0.0; ELSE RETURN 1.0; END IF;END //DELIMITER ;""")
Which creates the following traceback: Traceback (most recent call last): File "proof.py", line 21, in <module> DELIMITER ;""") File "build/bdist.macosx-10.5-i386/egg/MySQLdb/cursors.py", line 173, in execute File "build/bdist.macosx-10.5-i386/egg/MySQLdb/connections.py", line 35, in defaulterrorhandler_mysql_exceptions.ProgrammingError: (1064, "You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'DELIMITER //\nCREATE FUNCTION trivial_func (radius float) \n RETURNS FLOAT\n\n ' at line 1")
If I copy the same SQL directly into a mysql shell client, it works as expected Python mysql
| |
Bluish9, 4091269126. |
Asked Apr 13 '09 at A.M. too many u23k2070111 |
|
| |
Add a comment |
3 Answersactiveoldestvotes
| Up vote15down voteaccepted |
TheDELIMITERCommand is a MySQL shell client builtin, and it's recognized only by that program (and MySQL Query Browser). It's not necessary to useDELIMITERIf you execute SQL statements directly through an API. The purposeDELIMITERIs to help you avoid ambiguity about the termination ofCREATE FUNCTIONStatement, when the statement itself can contain semicolon characters. this is important in the shell client, where by default a semicolon terminates an SQL statement. you need to set the statement terminator to some other character in order to submit the body of a function (or trigger or procedure ). CREATE FUNCTION trivial_func (radius float) RETURNS FLOAT BEGIN IF radius > 1 THEN RETURN 0.0; <-- does this semicolon terminate RETURN or CREATE FUNCTION? ELSE RETURN 1.0; END IF;END
Since the API typically allows you to submit one SQL statement at a time, there's no ambiguity -- the interface knows that any semicolons inside the body of your function definition don't terminate the wholeCREATE FUNCTIONStatement. So there's no need to change the statement terminatorDELIMITER. |
