[DB-SIG] mysql string length?

[Lukasz Szybalski]

On 4/19/06, Andy Todd <andy47 at halfcooked.com> wrote:
> 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
> 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/
>