Page 1 of 2 12 Last
  • Jump to page:
    #1
  1. Contributing User
    Devshed Beginner (1000 - 1499 posts)

    Join Date
    Jun 2003
    Location
    Thessaloniki
    Posts
    1,396
    Rep Power
    16

    Question Escaping confusion with Python 3 + MySQL


    Okey, this is driving me nuts.

    I have tried every possible combo to escape '%s' but its just aint working for me.

    Code:
    				domain = host.split('.')[-2] + '.' + host.split('.')[-1]
    
    				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) )
    Can you PLEASE make this work? What am i doing wrong?
    What is now proved was once only imagined!
  2. #2
  3. Contributing User
    Devshed God 1st Plane (5500 - 5999 posts)

    Join Date
    Aug 2011
    Posts
    5,842
    Rep Power
    509
    Code:
    (pID, domain, ref, location, useros, browser, lastvisit, domain) = range(8)
    
    host='a.b.c.d.e.f.g'
    
    domain = '.'.join(host.split('.')[-2:])
    
    print('''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) )
    
    
    
    # prints
    # UPDATE visitors SET (pagesID, host, ref, location, useros, browser, visits) VALUES (0, f.g, 2, 3, 4, 5, 6) WHERE host LIKE "f.g"
    What would you like it to look like?
    [code]Code tags[/code] are essential for python code and Makefiles!
  4. #3
  5. No Profile Picture
    Contributing User
    Devshed Novice (500 - 999 posts)

    Join Date
    May 2009
    Posts
    652
    Rep Power
    39
    If you want to update records that contain "%s", the following code should return those records and hopefully give you a leg up on what to do.
    Code:
    ## insert "%s" into the SELECT statement
    'SELECT * FROM visitors WHERE Contains(host, "%s")' % ("%s")
    Last edited by dwblas; March 24th, 2017 at 01:55 PM.
  6. #4
  7. Contributing User
    Devshed Beginner (1000 - 1499 posts)

    Join Date
    Jun 2003
    Location
    Thessaloniki
    Posts
    1,396
    Rep Power
    16
    [Nice thoughn of printing it out, didnt even occured em that ai could print a cursor.execute!

    look what it print out for me when i

    Code:
    				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) )
    Code:
    UPDATE visitors SET (pagesID, host, ref, location, useros, browser, visits) VALUES (%s, %s, %s, %s, %s, %s, %s) WHERE host LIKE "%s" (1, 'cyta.gr', 'Άμεση Πρόσβαση', 'Greece', 'Windows', 'Chrome', '17-03-24 22:04:24', 'cyta.gr')
    What is now proved was once only imagined!
  8. #5
  9. Contributing User
    Devshed Beginner (1000 - 1499 posts)

    Join Date
    Jun 2003
    Location
    Thessaloniki
    Posts
    1,396
    Rep Power
    16
    but i use a comma within my cursor.select NOT a % for security.reason.
    What is now proved was once only imagined!
  10. #6
  11. Contributing User
    Devshed Beginner (1000 - 1499 posts)

    Join Date
    Jun 2003
    Location
    Thessaloniki
    Posts
    1,396
    Rep Power
    16
    and within the LIKE i really need to literal '%'

    the following doesnt seem to work. So we have 2 errors, the comma(although any other cur.exeucte i have works just fine with comma) and the "%%s%"

    Code:
    				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) )
    What is now proved was once only imagined!
  12. #7
  13. Contributing User
    Devshed Beginner (1000 - 1499 posts)

    Join Date
    Jun 2003
    Location
    Thessaloniki
    Posts
    1,396
    Rep Power
    16
    Tryign and trying everything this also failed:

    Code:
    				print('''UPDATE visitors SET (pagesID, host, ref, location, useros, browser, visits) VALUES (%s, %s, %s, %s, %s, %s, %s) WHERE host LIKE "%{}%" ''' % (pID, domain, ref, location, useros, browser, lastvisit, format(domain) ) )
    What is now proved was once only imagined!
  14. #8
  15. Contributing User
    Devshed Beginner (1000 - 1499 posts)

    Join Date
    Jun 2003
    Location
    Thessaloniki
    Posts
    1,396
    Rep Power
    16
    any thoughts please?
    What is now proved was once only imagined!
  16. #9
  17. No Profile Picture
    Contributing User
    Devshed Novice (500 - 999 posts)

    Join Date
    May 2009
    Posts
    652
    Rep Power
    39
    We have no idea what you want to do, only a statement that "this code does not work". Also, it appears that you did not read or try the code I posted so posting further would not be worthwhile.
  18. #10
  19. Contributing User
    Devshed Beginner (1000 - 1499 posts)

    Join Date
    Jun 2003
    Location
    Thessaloniki
    Posts
    1,396
    Rep Power
    16
    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?

    it must be somehting specific that has to do with pyMySQL....
    What is now proved was once only imagined!
  20. #11
  21. Contributing User
    Devshed Beginner (1000 - 1499 posts)

    Join Date
    Jun 2003
    Location
    Thessaloniki
    Posts
    1,396
    Rep Power
    16
    Ths is the whole snippert i'am trying so you can see what i'm tryong to do.

    Code:
    		# if bot is contained in hostname update all previous database bot hostname entries with current bot hostname
    		for bot in bots:
    			if bot in host:
    				domain = '.'.join( host.split('.')[-2:] )
    
    				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) )
    				if cur.rowcount():
    					botfound = True
    					break
    As if i have a syntactical error in someplace which i'am faling to see or PyMySQL wants special treatment regarding escaping special characters.
    What is now proved was once only imagined!
  22. #12
  23. Contributing User
    Devshed Beginner (1000 - 1499 posts)

    Join Date
    Jun 2003
    Location
    Thessaloniki
    Posts
    1,396
    Rep Power
    16
    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) )
    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
    What is now proved was once only imagined!
  24. #13
  25. Contributing User
    Devshed Beginner (1000 - 1499 posts)

    Join Date
    Jun 2003
    Location
    Thessaloniki
    Posts
    1,396
    Rep Power
    16
    same kind of error with this attempt also:

    Code:
    cur.execute('''UPDATE visitors SET (pagesID, host, ref, location, useros, browser, visits) VALUES ({}, {}, {}, {}, {}, {}, {}) WHERE host LIKE "{}"'''.format(pID, domain, ref, location, useros, browser, lastvisit, domain) )
    What is now proved was once only imagined!
  26. #14
  27. Contributing User
    Devshed Beginner (1000 - 1499 posts)

    Join Date
    Jun 2003
    Location
    Thessaloniki
    Posts
    1,396
    Rep Power
    16
    [code]
    cur.execute('''UPDATE visitors SET (pagesID, host, ref, location, useros, browser, visits) VALUES ({}, {}, {}, {}, {}, {}, {}) WHERE host LIKE "%{}%" '''.format(pID, domain, ref, location, useros, browser, lastvisit, domain) )
    [code]

    display this in the 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
    What is now proved was once only imagined!
  28. #15
  29. Contributing User
    Devshed God 1st Plane (5500 - 5999 posts)

    Join Date
    Aug 2011
    Posts
    5,842
    Rep Power
    509
    returning to fundamentals, you might try
    Code:
    ################ test setup
    
    (pID, domain, ref, location, useros, browser, lastvisit, domain) = range(8)
    
    host='a.b.c.d.e.f.g'
    
    domain = '.'.join(host.split('.')[-2:])
    
    class c:
    	'''class without a callable string method'''
    	__str__ = None
    
    browser = c()
    
    ################ insert before cur.execute()
    
    for v in 'pID\t,domain\t,ref\t,location,useros\t,browser\t,lastvisit,domain\t'.split(','):
    	print('testing', v, end = '\t')
    	try:
    		o = eval(v)
    	except:
    		print('eval failed')
    	else:
    		print(type(o), end = '\t')
    		print(('\tno no no no no str method!', 'has str method')[('__str__' in dir(o)) and callable(o.__str__)])
    [code]Code tags[/code] are essential for python code and Makefiles!
Page 1 of 2 12 Last
  • Jump to page:

IMN logo majestic logo threadwatch logo seochat tools logo