On 4/19/06, Andy Todd <andy47@[...].com>  wrote:
>  Lukasz Szybalski wrote:
>  > On 4/18/06, Lukasz Szybalski <szybalski@[...].com> wrote:
>  >> On 4/17/06, engelbert.gruber@[...].at <engelbert.gruber@[...].at> wrote:
>  >>> On Mon, 17 Apr 2006, Andy Todd wrote:
>  >>>
>  >>>>>>>>> stmt = "INSERT INTO table_x (body) VALUES (%s)" # [1]
>  >>>>>>>>> cursor.execute(stmt, (body,))
>  >>>>>> [1] note that there are no quote marks around the %s
>  >>>>> this works here (debian testing, python 2.3.5, 4.0.21) ::
>  >>>>>
>  >>>>> import MySQLdb
>  >>>>> """
>  >>>>> create table table_x (
>  >>>>>    id int auto_increment primary key,
>  >>>>>    body text
>  >>>>>    )
>  >>>>> """
>  >>>>> db = MySQLdb.connect(passwd="",db="test")
>  >>>>> c = db.cursor()
>  >>>>> body = "0....5...."*28
>  >>>>> sql = "INSERT INTO table_x (body) VALUES('%s')"
>  >>>>> c.execute( sql % (body))
>  >>>>> c.execute("select body from table_x")
>  >>>>> for row in c.fetchall():
>  >>>>>      print len(row[0])
>  >>>>>
>  >>>>> i wont bet on fetching 1Mb.
>  >>>>>
>  >>>>> cheers
>  >>>> You've just repeated the original poster's code. As I suggested the problem
>  >>>> is that he's using string substitution when he should be using parameter
>  >>>> substitution. In these cases %s means two completely different things.
>  >>> yes but it works here, even with ``body = "0....5...."*280`` so i cannot
>  >>> blame it on not using 'paramstyle', or what ?
>  >>>
>  >>>> Please read the sections on 'paramstyle' and the cursor '.execute' method in
>  >>>> the DB-API 2.0 definition [1].
>  >>> of course you are right it is ::
>  >>>
>  >>>    c.execute("INSERT INTO table_x (body) VALUES('%s')"% (body))
>  >>>
>  >>> or paramstyle without quotes ::
>  >>>
>  >>>    c.execute(INSERT INTO table_x (body) VALUES(%s)", (body))
>  >>>
>  >>> but both work here with bodylength up to 65535 bytes not 255.
>  >>>
>  >>> sorry for dupplication, but i fail to see the point.
>  >>>
>  >>>
>  >> Ok, I'm almost there. Thank you.
>  >> So i finally realized that the length is not a problem but the actual
>  >> body. The body includes characters such as " ' ", so ( I'm, I'll, etc)
>  >> which cause it to give an error.
>  >>
>  >>>>> body="helo I'm here,I'll be there"
>  >>>>> body=body *15
>  >> When i try :
>  >> c.execute("Insert into table_x(body) VALUES(%s)",(body))
>  >> This will work but how do i make :
>  >>
>  >> sql="Insert into table_x(body) VALUES(%s)",(body)
>  >> c.execute(sql)  #complains that i give it a tuple, which i did when
>  >> you look at sql
>  >>
>  >> One problem i see here is that it works in one way but no the other.
>  >> So how do i add id and  make it work both ways?
>  >>
>  >> c.execute("Insert into  table_x(id,body) VALUES(%d,%s)",(id),(body))
>  >> ??does not work
>  >>
>  >> I'm not familiar with ('pyformat'      Python extended format codes,
>  >> e.g. '...WHERE name=%(name)s' ), therefore i would prefer to use
>  >> "insert into table_x(id,body)VALUES('%d','%s')" % (id,body)
>  >>
>  >> -How to account for " I'll " in a body?
>  >> -When would i HAVE to use pyformat? Can everything be done in
>  >> 'format'        ANSI C printf format codes,  e.g. '...WHERE name=%s'
>  >>
>  > Ok guys, thanks for help. I finally search for how to scape strings and i used:
>  >
>  > "insert into table(id,body) values ('%d',"'''"%s"'''")"%(id,body)
>  >
>  > where "'''" on each side of s  = double quote + 3x single quote+ double quote
>  >
>  > Thanks again
>  >> Thanks,
>  >> Lukasz
>  >>
>  >
>  >
>  > --
>  > Lukasz Szybalski
>  > www.lucasmanual.com
> 
> 
>  No, no, no, no. Do not use string substitution. I was trying to be
>  subtle in my earlier messages but that didn't seem to work. In MySQLdb
>  %s is a parameter substitution character NOT string substitution. Please
>  repeat this thirty times and then come back and read the rest of this
>  message.
> 
>  ...
> 
>  You've done it, right?
> 
>  OK. Your code examples re-written to use parameter substitution and not
>  string substitution are;
> 
>   >>> c.execute("Insert into table_x(body) VALUES(%s)",(body))
> 
>  This is good.
> 
>   >>> sql="Insert into table_x(body) VALUES(%s)",(body)
>   >>> c.execute(sql)  #complains that i give it a tuple, which i did when
>  you look at sql
> 
>  this should be;
> 
>   >>> sql = "INSERT INTO table_x (body) VALUES (%s)"
>   >>> c.execute(sql, (body,))
> 
>  Note that you do parameter substitution when you execute your statement
>  not when you assign it to a variable. Also note that the execute method
>  expects a tuple hence the comma after 'body'.
> 
>   >>> c.execute("Insert into  table_x(id,body)
>  VALUES(%d,%s)",(id),(body)) ??does not work
> 
>  And I'm very glad that it doesn't. You could do this as;
> 
>   >>> c.execute("Insert into  table_x(id,body) VALUES(%s, %s)",(id, body))
> 
>  Or
> 
>   >>> sql = "INSERT INTO table_x (id, body) VALUES (%s, %s)"
>   >>> c.execute(sql, (id, body))


Sounds good. And just to make it even more clear, if i had 5 columns then :

col3='hello'
col4='bye'
col5='example'

sql = "insert into table_x( id, body, col3, col4, col5) values(%s,%s,%s,%s,%s)"
c.execute(sql, (id,body,col3,col4,col5))

Great, this object/parameter substitution works like magic!

Thanks again.
Lukasz

>  Note that in my versions of your code the substitution character is
>  always %s. This does not mean that it's a string, MySQLdb is smart
>  enough to figure out the underlying column's datatype and map whatever
>  you provide (within reason of course) as part of the parameter substitution.
> 
>   >>> "insert into table(id,body) values ('%d',"'''"%s"'''")"%(id,body)
> 
>  This is just not very good at all. If you carry on like this your code
>  will almost entirely consist of single and double quotes and you will
>  likely go stark raving mad.
> 
>  Once more, %s is simply a marker in the statement you provide to the
>  execute method which says "substitute the next item from the tuple I've
>  provided here please". It is not string substitution.
> 
>  Now hands up for changing the DB-API to remove the pyformat paramstyle?
>  If we're going to have options I think we should restrict them to either
>  named parameters (a la cx_Oracle) or the '?' qmark (a la ODBC and JDBC).
> 
>  Regards,
>  Andy
>  --
>  --------------------------------------------------------------------------------
>   From the desk of Andrew J Todd esq - http://www.halfcooked.com/
> 
_______________________________________________
DB-SIG maillist  -  DB-SIG@[...].org
http://mail.python.org/mailman/listinfo/db-sig