On 4/17/06, [EMAIL PROTECTED] <[EMAIL PROTECTED]> 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))
Don't do that. It'll work right up until you have a string with quotes
or \x00 bytes in it. Then bad things will happen. How bad depends on
where body comes from (think SQL injection attacks).
> or paramstyle without quotes ::
>
> c.execute(INSERT INTO table_x (body) VALUES(%s)", (body))
Do that instead. Special characters will be escaped.
> but both work here with bodylength up to 65535 bytes not 255.
TEXT columns are not the same as CHAR or VARCHAR. TEXT may be very
large (they are non-binary BLOBs, but LOB sounds silly). CHAR/VARCHAR
may be at most 255 characters. However, there are really four types of
TEXT: TINYTEXT, TEXT, MEDIUMTEXT, and LONGTEXT. TEXT supports up to
65535 characters (16-bit length). If a 255-character limit is being
hit, it's most likely either CHAR/VARCHAR or possibly TINYTEXT.
--
The Pythonic Principle: Python works the way it does
because if it didn't, it wouldn't be Python.
_______________________________________________
DB-SIG maillist - DB-SIG@python.org
http://mail.python.org/mailman/listinfo/db-sig
