Error creating table in MYSQL using Python

Question

I am trying to create a MySQL table using python but it keeps giving me this error: Error while connecting to MySQL 1064 (42000): 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 ' close FLOAT, high FLOAT, low FLOAT, open FLOAT, volume FLOAT, instrument CHA...' at line 1

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

datetime is a type and likely a reserved keyword. smalldatetime is not a type in mariadb (I beleive it's a type in sql server). Maybe instead: date_time datetime. — JNevill
– JNevill, Commented Apr 7, 2022 at 18:59
I just tried this. Still giving me this error : Error while connecting to MySQL 1064 (42000): 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 '' at line 1 — Shully
– Shully, Commented Apr 8, 2022 at 7:51

Andrej Prsa · Accepted Answer · 2022-04-08 17:41:06Z

1

It is because there is no such data type as smalldatetime. You probably meant to say:

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

Comment follow-up.

To use mariadb connector, you would do something like this:

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

At this point the cursor.execute() line should work fine.

edited Apr 8, 2022 at 17:41

answered Apr 7, 2022 at 18:59

Andrej Prsa

5583 silver badges16 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

Shully Over a year ago

I'm still getting an error : Error while connecting to MySQL 1064 (42000): 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 '' at line 1

Andrej Prsa Over a year ago

What is the error when you run the command in mysql directly? I'm using mariadb 15.1 and it works fine.

Shully Over a year ago

I just ran it through mysql directly and it worked. I just wonder why it isn't going through using Python.

Andrej Prsa Over a year ago

I'm using mariadb connector (pip install mariadb) and it works like a charm. I'll edit my response to include that information.

Shully Over a year ago

I just figured out the problem. I forgot a closing bracket at the end of the CREATE TABLE query. Thanks for all your help.

Collectives™ on Stack Overflow

Error creating table in MYSQL using Python

1 Answer 1

Comment follow-up.

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comment follow-up.

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related