[FIXED] How to solve 1064 error PyMySQL in Jupyter notebook?

Issue

I am using PyMySQLto create a simple table as a practice, but I can't create the table and getting this error: "ProgrammingError: (1064, "You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax"

Can anyone point my mistake?

The code:

# CREATE TABLE
# open connection
connection = pymysql.connect(cnx['host'],cnx['username'],cnx['password'],cnx['db'])
# open cursor
cursor = connection.cursor()
# query for creating table
create_table = """
                CREATE TABLE classroom (
                    student_id INTEGER PRIMARY KEY,
                    name VARCHAR(20),
                    gender CMAR(1),
                    physics_marks INTEGER,
                    chemistry_marks INTEGER,
                    mathematics_marks INTEGER
                 );"""
# execute query
cursor.execute (create_table)
# commit changes
connection.commit()
# close connection
connection.close()

The error:

---------------------------------------------------------------------------
ProgrammingError                          Traceback (most recent call last)
<ipython-input-34-358e0fa696d2> in <module>
     15                  );"""
     16 # execute query
---> 17 cursor.execute (create_table)
     18 # commit changes
     19 connection.commit()

~\AppData\Local\Continuum\anaconda3\lib\site-packages\pymysql\cursors.py in execute(self, query, args)
    168         query = self.mogrify(query, args)
    169 
--> 170         result = self._query(query)
    171         self._executed = query
    172         return result

~\AppData\Local\Continuum\anaconda3\lib\site-packages\pymysql\cursors.py in _query(self, q)
    326         self._last_executed = q
    327         self._clear_result()
--> 328         conn.query(q)
    329         self._do_get_result()
    330         return self.rowcount

~\AppData\Local\Continuum\anaconda3\lib\site-packages\pymysql\connections.py in query(self, sql, unbuffered)
    515                 sql = sql.encode(self.encoding, 'surrogateescape')
    516         self._execute_command(COMMAND.COM_QUERY, sql)
--> 517         self._affected_rows = self._read_query_result(unbuffered=unbuffered)
    518         return self._affected_rows
    519 

~\AppData\Local\Continuum\anaconda3\lib\site-packages\pymysql\connections.py in _read_query_result(self, unbuffered)
    730         else:
    731             result = MySQLResult(self)
--> 732             result.read()
    733         self._result = result
    734         if result.server_status is not None:

~\AppData\Local\Continuum\anaconda3\lib\site-packages\pymysql\connections.py in read(self)
   1073     def read(self):
   1074         try:
-> 1075             first_packet = self.connection._read_packet()
   1076 
   1077             if first_packet.is_ok_packet():

~\AppData\Local\Continuum\anaconda3\lib\site-packages\pymysql\connections.py in _read_packet(self, packet_type)
    682 
    683         packet = packet_type(buff, self.encoding)
--> 684         packet.check_error()
    685         return packet
    686 

~\AppData\Local\Continuum\anaconda3\lib\site-packages\pymysql\protocol.py in check_error(self)
    218             errno = self.read_uint16()
    219             if DEBUG: print("errno =", errno)
--> 220             err.raise_mysql_exception(self._data)
    221 
    222     def dump(self):

~\AppData\Local\Continuum\anaconda3\lib\site-packages\pymysql\err.py in raise_mysql_exception(data)
    107         errval = data[3:].decode('utf-8', 'replace')
    108     errorclass = error_map.get(errno, InternalError)
--> 109     raise errorclass(errno, errval)

ProgrammingError: (1064, "You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'CMAR(1),\n                    physics_marks INTEGER,\n                    chemistr' at line 4")

Solution

Been a while since I have played with MySQL with python... Looks like some of the table field keys are incorrect (From what I'm used to seeing). Maybe try this and see how you go.

create_table = """
            CREATE TABLE classroom (
            student_id INT(11) AUTO_INCREMENT PRIMARY KEY,
            name VARCHAR(20) NOT NULL,
            gender CHAR(1) NOT NULL,
            physics_marks INT(4) NOT NULL,
            chemistry_marks INT(4) NOT NULL,
            mathematics_marks INT(4) NOT NULL,
            );"""

Answered By - Cory C