Escaping confusion with Python 3 + MySQL

[Joel Goldstick]

On Sun, Mar 26, 2017 at 11:48 AM, Νίκος Βέργος <me.on.nzt at gmail.com> wrote:
> Τη Κυριακή, 26 Μαρτίου 2017 - 6:39:01 μ.μ. UTC+3, ο χρήστης Steve D'Aprano έγραψε:
>> On Mon, 27 Mar 2017 02:11 am, Νίκος Βέργος wrote:
>>
>> > 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")
>>
>> Start by following the instructions given:
>>
>>     check the manual that corresponds to your MariaDB server version
>>     for the right syntax to use
>>
>> just like the error message tells you to do.
>>
>> Are you sure that the domain needs to have leading and trailing percentage
>> signs? "%cyta.gr%" instead of "cyta.gr"?
>>
>> Are you sure that the LIKE clause needs double quotes?
>>
>>     LIKE "%s"
>>
>> Perhaps MariaDB requires single quotes:
>>
>>     LIKE '%s'
>>
>> or perhaps no quotes at all:
>>
>>     LIKE %s
>>
>>
>> But I'm just guessing, because I haven't read the MariaDB manual. You should
>> do so.
>>
>>
>> > which you can see at http://superhost.gr
>>
>> Ah, Nikos, its been a long time! I thought I recognised your style of
>> posting.

Not long enough!
>
>
> Howdy Steve!
> Yes its me and yes its have been a long time! How are you?!
>
> Yeap still trying to make my webiste better and better every day.
>
> As for MariaDB i tried with single/double/no_quoting at all and it still produces
> 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")
>
> I think that Ian Kelly is right and it has soemthign to do with the databse wrapper not substituting properly the actual string contain in the LIKE clause between the double quotes.
>
> Perhaps i need to change pymysql with some other database interface or there is something other than that?
> --
> https://mail.python.org/mailman/listinfo/python-list



-- 
Joel Goldstick
http://joelgoldstick.com/blog
http://cc-baseballstats.info/stats/birthdays