Escaping confusion with Python 3 + MySQL

[Chris Angelico]

On Mon, Mar 27, 2017 at 2:11 AM, Νίκος Βέργος <me.on.nzt at gmail.com> wrote:
> Τη Κυριακή, 26 Μαρτίου 2017 - 5:49:00 μ.μ. UTC+3, ο χρήστης Ian έγραψε:
>
>> The database wrapper won't do substitution into the middle of a string
>> like that. Either concatenate the literal %'s on in the SQL statement
>> or add them to the string before you pass it in, i.e. '%' + domain +
>> '%' or '%%%s%%' % domain or '%{}%'.format(domain).
>
> I just tried:
>
> domain = '.'.join( host.split('.')[-2:] )
> domain = '%' + domain + '%'
>
> cur.execute('''UPDATE visitors SET (pagesID, host, ref, location, useros, browser, visits) VALUES (%s, %s, %s, %s, %s, %s, %s) WHERE host LIKE "%s" ''',
>                                                                                                                                                                                                                                                                                         (pID, domain, ref, location, useros, browser, lastvisit, domain) )
>
>
> and i received no error in the error_log but
> ProgrammingError(1064, "You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '(pagesID, host, ref, location, useros, browser, visits) VALUES (1, '%cyta.gr%', ' at line 1")
>
> which you can see at http://superhost.gr
>
> You said somethign about concatenating the literal % in the SQL to which i didnt actually i understand how to implement.

Stop panicking.

Stop just trying one thing very quickly, getting an error message, and
firing a post back to this list.

Slow down and read the messages you are getting.

You have already been told about UPDATE queries not using this syntax,
and the error message is referring to that. You have an error in your
SQL syntax; **check the manual**.

This is the same problem that you had the last time you were here on
this list - you had a different email address then, and I can't
remember what name you were going by, but I won't quickly forget
superhost.gr, which at the moment looks like Geocities found a new
domain name.

ChrisA