错误使用Python在MySQL中创建表

Question

我正在尝试使用 python 创建 MySQL 表，但它一直给我这个错误：连接到 MySQL 1064 (42000) 时出错：您的 SQL 语法中有错误；检查与您的 MariaDB 服务器版本相对应的手册，了解在第 1 行的“close FLOAT、high FLOAT、low FLOAT、open FLOAT、volume FLOAT、instrument CHA...”附近使用的正确语法

import mysql.connector
from mysql.connector import Error

#connecting to database
try:
    connection = mysql.connector.connect(host = "localhost",
                                         user = "root",
                                         database="hindalco"
                                         )

    #cursor method used to perform SQL operations
    cursor = connection.cursor()
    cursor.execute("CREATE TABLE tickersymbol (id INT AUTO_INCREMENT PRIMARY KEY, datetime smalldatetime, close FLOAT, high FLOAT, low FLOAT, open FLOAT, volume FLOAT, instrument CHAR(10)")


#print error messages using error as object
except Error as e:
    print("Error while connecting to MySQL", e)

#close open connections after work is complete
finally:
    if connection.is_connected():
        connection.close()
        print("MySQL connection is closed")

Answer 1

这是因为没有这样的数据类型为smalldateTime。您可能是要说：

cursor.execute("CREATE TABLE tickersymbol (id INT AUTO_INCREMENT PRIMARY KEY, smalldatetime datetime, close FLOAT, high FLOAT, low FLOAT, open FLOAT, volume FLOAT, instrument CHAR(10)")

评论后续行动。

要使用MariaDB连接器，您将执行类似的操作：

import mariadb as mdb

dbconfig = {
    'host': 'localhost',
    'user': 'username',
    'password': 'password',
    'database': 'dbname'
}

try:
    db = mdb.connect(**dbconfig)
except mdb.Error as e:
    raise(f'error connecting: {e}')

# A matter of personal preference, I like queries to be returned as dictionaries, and I like auto-commits to the database:
cursor = db.cursor(dictionary=True)
db.autocommit = True

此时，cursor.execute（）行应该正常工作。