jupyter-notebook - 如何解决 Jupyter notebook 中的 1064 错误 PyMySQL？

Question

我正在使用 PyMySQL 创建一个简单的表作为练习，但是我无法创建表并收到此错误：“ProgrammingError: (1064,”您的 SQL 语法有错误；请查看与您的 MySQL 服务器相对应的手册正确语法的版本”

谁能指出我的错误？

编码：

# 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()

错误：

---------------------------------------------------------------------------
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")

标签： jupyter-notebookcreate-tablepymysql

Answer 1

自从我用 python 玩 MySQL 以来已经有一段时间了……看起来有些表字段键不正确（从我以前看到的）。也许试试这个，看看你怎么样。

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,
            );"""