pymysql can't do SELECT * - Printable Version

pymysql can't do SELECT * - Printable Version

+- Python Forum (https://python-forum.io)
+-- Forum: Python Coding (https://python-forum.io/forum-7.html)
+--- Forum: General Coding Help (https://python-forum.io/forum-8.html)
+--- Thread: pymysql can't do SELECT * (/thread-34882.html)

pymysql can't do SELECT * - Pedroski55 - Sep-11-2021

I use pymysql to collect homework. Works great:

def mysqlRemote(clas, weeknr): 
    # To connect remote MySQL database 
    conn = pymysql.connect( 
        host='123.456.789.111', 
        user='myself',  
        password = 'secret', 
        db='mydb', 
        ) 
      
    cur = conn.cursor()

    sql = f"SELECT studentnr, score FROM allstudentsAnswers{clas}CW WHERE weeknr = %s"
    cur.execute(sql, (weeknr,))
    output = cur.fetchall() 
         
    # To close the connection 
    conn.close()
    return output

Last week, something went wrong in PHP and the marking was wrong. So I thought, "Fetch everything and mark it with Python."

When I try SELECT *

def mysqlRemote(clas, weeknr): 
    # To connect remote MySQL database 
    conn = pymysql.connect( 
        host='123.456.789.111', 
        user='myself',  
        password = 'secret', 
        db='mydb', 
        ) 
      
    cur = conn.cursor()

    sql = f"SELECT * FROM allstudentsAnswers{clas}CW WHERE weeknr = %s"
    cur.execute(sql, (weeknr,))
    output = cur.fetchall() 
    
    # To close the connection 
    conn.close()
    return output

I get an error:

Error:Traceback (most recent call last):
  File "/usr/lib/python3.8/idlelib/run.py", line 559, in runcode
    exec(code, self.locals)
  File "<pyshell#11>", line 1, in <module>
  File "<pyshell#10>", line 13, in mysqlRemote
  File "/home/pedro/.local/lib/python3.8/site-packages/pymysql/cursors.py", line 148, in execute
    result = self._query(query)
  File "/home/pedro/.local/lib/python3.8/site-packages/pymysql/cursors.py", line 310, in _query
    conn.query(q)
  File "/home/pedro/.local/lib/python3.8/site-packages/pymysql/connections.py", line 548, in query
    self._affected_rows = self._read_query_result(unbuffered=unbuffered)
  File "/home/pedro/.local/lib/python3.8/site-packages/pymysql/connections.py", line 775, in _read_query_result
    result.read()
  File "/home/pedro/.local/lib/python3.8/site-packages/pymysql/connections.py", line 1163, in read
    self._read_result_packet(first_packet)
  File "/home/pedro/.local/lib/python3.8/site-packages/pymysql/connections.py", line 1236, in _read_result_packet
    self._read_rowdata_packet()
  File "/home/pedro/.local/lib/python3.8/site-packages/pymysql/connections.py", line 1274, in _read_rowdata_packet
    rows.append(self._read_row_from_packet(packet))
  File "/home/pedro/.local/lib/python3.8/site-packages/pymysql/connections.py", line 1290, in _read_row_from_packet
    data = data.decode(encoding)
UnicodeDecodeError: 'utf-8' codec can't decode byte 0x93 in position 17: invalid start byte

What can I do about this error?? I don't really understand it.

RE: pymysql can't do SELECT * - Larz60+ - Sep-11-2021

add print(f"sql: {sql}") after line 12
Does this query run from mysql MySQL command line client.
does it work as expected?

RE: pymysql can't do SELECT * - jefsummers - Sep-11-2021

It looks to me like a bad or unexpected byte in a field of data that is not included in the first query. For example, since it is trying to read as UTF-8, perhaps one of the fields is a blob or image. You could try querying on each field until you find which one is the culprit.

RE: pymysql can't do SELECT * - Pedroski55 - Sep-11-2021

Thanks for the tips!

print(f"sql: {sql}") gives:

Quote:sql: SELECT * FROM allstudentsAnswers20BECW WHERE weeknr = %s

This, for example, works fine:

Quote:sql = f"SELECT studentnr, score, Answer1, Answer2 FROM allstudentsAnswers{clas}CW WHERE weeknr = %s"

I made a query for all the columns I want:

sql = 'SELECT studentnr, Answer1, Answer2, Answer3, ...... , Answer61 FROM allstudentsAnswers20BECW WHERE weeknr = \'Week1\''

but that fails with the same error. Something weird in there!

Doesn't matter, I just exported the whole table from MySQL as .csv and got what I needed from there!

A mystery!