Escaping confusion with Python 3 + MySQL

[Νίκος Βέργος]

Τη Κυριακή, 26 Μαρτίου 2017 - 4:39:44 μ.μ. UTC+3, ο χρήστης MeV έγραψε:
> On Sunday, March 26, 2017 at 6:34:30 AM UTC-7, Νίκος Βέργος wrote:
> > with import cgitb; cgitb.enable()
> > 
> > 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")
> > 
> > that is all i get form error. error_log doesnt produce errors when iam trying
> > 
> > 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) )
> > 
> > WHY the valued aren't getting substituted wi the aeguments i give it in to work with?
> 
> The % construct is Python 2 and no longer supported in Python 3. You should read up on the "{}" and format method.
> 
> https://docs.python.org/3/tutorial/inputoutput.html#fancier-output-formatting

[code]
cur.execute('''UPDATE visitors SET (pagesID, host, ref, location, useros, browser, visits) VALUES ({}, {}, {}, {}, {}, {}, {}) WHERE host LIKE "{}"''', 
																																			(pID, domain, ref, location, useros, browser, lastvisit, domain) )
[/code]

now has the following response inside error_log

[code]
[Sun Mar 26 16:45:50.940077 2017] [cgi:error] [pid 2337] [client 178.59.182.161:13755] AH01215: Error in sys.excepthook:
[Sun Mar 26 16:45:50.940126 2017] [cgi:error] [pid 2337] [client 178.59.182.161:13755] AH01215: Traceback (most recent call last):
[Sun Mar 26 16:45:50.940151 2017] [cgi:error] [pid 2337] [client 178.59.182.161:13755] AH01215:   File "/usr/local/lib/python3.5/cgitb.py", line 268, in __call__
[Sun Mar 26 16:45:50.940166 2017] [cgi:error] [pid 2337] [client 178.59.182.161:13755] AH01215:     self.handle((etype, evalue, etb))
[Sun Mar 26 16:45:50.940189 2017] [cgi:error] [pid 2337] [client 178.59.182.161:13755] AH01215:   File "/usr/local/lib/python3.5/cgitb.py", line 273, in handle
[Sun Mar 26 16:45:50.940200 2017] [cgi:error] [pid 2337] [client 178.59.182.161:13755] AH01215:     self.file.write(reset())
[Sun Mar 26 16:45:50.940220 2017] [cgi:error] [pid 2337] [client 178.59.182.161:13755] AH01215: ValueError: underlying buffer has been detached
[Sun Mar 26 16:45:50.940225 2017] [cgi:error] [pid 2337] [client 178.59.182.161:13755] AH01215: 
[Sun Mar 26 16:45:50.940235 2017] [cgi:error] [pid 2337] [client 178.59.182.161:13755] AH01215: Original exception was:
[Sun Mar 26 16:45:50.940249 2017] [cgi:error] [pid 2337] [client 178.59.182.161:13755] AH01215: Traceback (most recent call last):
[Sun Mar 26 16:45:50.940265 2017] [cgi:error] [pid 2337] [client 178.59.182.161:13755] AH01215:   File "metrites.py", line 355, in <module>
[Sun Mar 26 16:45:50.940298 2017] [cgi:error] [pid 2337] [client 178.59.182.161:13755] AH01215:     (pID, domain, ref, location, useros, browser, lastvisit, domain) )
[Sun Mar 26 16:45:50.940329 2017] [cgi:error] [pid 2337] [client 178.59.182.161:13755] AH01215:   File "/usr/local/lib/python3.5/site-packages/pymysql/cursors.py", line 144, in execute
[Sun Mar 26 16:45:50.940343 2017] [cgi:error] [pid 2337] [client 178.59.182.161:13755] AH01215:     query = self.mogrify(query, args)
[Sun Mar 26 16:45:50.940379 2017] [cgi:error] [pid 2337] [client 178.59.182.161:13755] AH01215:   File "/usr/local/lib/python3.5/site-packages/pymysql/cursors.py", line 135, in mogrify
[Sun Mar 26 16:45:50.940398 2017] [cgi:error] [pid 2337] [client 178.59.182.161:13755] AH01215:     query = query % self._escape_args(args, conn)
[Sun Mar 26 16:45:50.940420 2017] [cgi:error] [pid 2337] [client 178.59.182.161:13755] AH01215: TypeError: not all arguments converted during string formatting
[/code]