[DB-SIG] mysql string length?

Andy Todd andy47 at halfcooked.com
Wed Apr 19 09:45:17 CEST 2006

Lukasz Szybalski wrote:
> On 4/18/06, Lukasz Szybalski <szybalski at gmail.com> wrote:
>> On 4/17/06, engelbert.gruber at ssg.co.at <engelbert.gruber at ssg.co.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 


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))


 >>> sql = "INSERT INTO table_x (id, body) VALUES (%s, %s)"
 >>> c.execute(sql, (id, body))

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).

 From the desk of Andrew J Todd esq - http://www.halfcooked.com/

More information about the DB-SIG mailing list