[DB-SIG] mysql string length?

[Lukasz Szybalski]

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