trying to run this command to create a database with mysql but i get a "SQL Syntax error"

user_19847563 0
Viewed 40

This is the error:
mysql.connector.errors.ProgrammingError: 1064 (42000): 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 'database' at line 1

Here is the code that I use to create the database:

import mysql.connector

mydb = mysql.connector.connect(
    host="localhost",
    user="root",
    passwd = "password12",
    )

my_cursor = mydb.cursor()

my_cursor.execute("CREATE DATABASE database")

my_cursor.execute("SHOW DATABASES")
for db in my_cursor:
    print(db)

I tried using a different name for the database, but I still got the same error.

mysql python
1 Answers

You have an extraneous comma after your password parameter. Change your mydb connection string to the following:

import mysql.connector

mydb = mysql.connector.connect(
    host = 'localhost',
    user = 'root',
    password = 'password12'
    )

my_cursor = mydb.cursor()

my_cursor.execute("CREATE DATABASE database")

my_cursor.execute("SHOW DATABASES")
for db in my_cursor:
    print(db)

You should use the word password over passwd. Even though they're synonymous, according to the MySQL documentation here: Connector/Python Connection Arguments

An asterisk (*) following an argument indicates a synonymous argument name, available only for compatibility with other Python MySQL drivers. Oracle recommends not to use these alternative names.

Argument Name Default Description
user (username*) The user name used to authenticate with the MySQL server.
password (passwd*) The password to authenticate the user with the MySQL server.
password1, password2, and password3 For Multi-Factor Authentication (MFA); password1 is an alias for password. Added in 8.0.28.
database (db*) The database name to use when connecting with the MySQL server.
host 127.0.0.1 The host name or IP address of the MySQL server.

Also, as mentioned in the comments above, you should not use the word database as your actual database name. I understand you may be doing it as an example, but its worth noting that it is a Reserved Keyword.

user_30928470
Related