Python MySQL CREATE DATABASE

You can create new databases using the Python MySQL CREATE DATABASE command. The syntax for this command is as shown below. You can actually have many databases at any given point.

CREATE DATABASE database_name

Fun Fact: The SQL syntax never changes. Whichever language you use MySQL in, the syntax shown above will always remain the same. Even if you directly use SQL in a database, the syntax is as shown above.

Example

The following code first connects to the database successfully, and accordingly using the returned MySQL object a cursor is created. This cursor is then used to create a database called “database1”.

import mysql.connector

db = mysql.connector.connect(
    host = 'localhost',
    user = 'user_name',
    password = 'password')

cursor = db.cursor()
cursor.execute("CREATE DATABASE database1")

Handling Multiple Databases

Displaying list of existing databases

Unless you’re using a database front-end like phpmyadmin, you will need to use the SHOW DATABASES command to list the existing databases. Once the command is executed, you can iterate over the cursor object to retrieve all the database names or use the fetchall() command.

import mysql.connector

db = mysql.connector.connect(
  host = "localhost",
  user =  "user_name",
  password = "password"
)

cursor = db.cursor()
cursor.execute("SHOW DATABASES")

#Method1
for x in cursor:
  print(x)

#Method2
print(cursor.fetchall())

#Closes the connection
db.close()

Adding the close() function is not compulsory, as Python will shut it down for you if you don’t, but it’s good practice.

Connecting to a Existing Database

Once you know the names of the databases that exist, you can connect directly to one of them. All you have to do is add an extra parameter when using the mysql.connector.

import mysql.connector

db = mysql.connector.connect(
  host = "localhost",
  user =  "user_name",
  password = "password"
  database = "testdb"
)

This can also be used as a way to check if a specific database exists. If the connect command fails, you know that the database doesn’t exist.

This marks the end of our Python MySQL CREATE DATABASE Article. Let us know if you have any suggestions or corrections to make.

Use this link to head back to main Python MySQL page: link