Title: Create databases and get database information from MariaDB in Python

My post Get started using MariaDB in Python explaine dhow to use HeidiSQL to create a MariaDB database and put some tables with data in it. I mentioned in that post that you can do all of that with Python, so here's the first step in doing that. This post explains how you cna get information about MariaDB databases and how you can create a database with Python.

I've written a LOT of database programs over the years in several programming languages. On thing that all of those programs have in common is that they isolate the main program by putting database code in small functions. It's much easier to understand simple function calls. It's much easier to understand simple function calls than it is to understand a huge pile of database code. So you'll see the code in this example (and others that come later) broken into functions.

Seeing if a Database Exists

Fortuately you can also use a LIKE clause, and that one doesn't need to know the name of the firld that you're fetching.

Here's a function that uses SHOW DATABASES LIKE to see if a database exists.

def database_exists(cur, db_name): '''Return True if the database exists.''' # Notice there are no wild cards so this requires an exact match. cur.execute(f'SHOW DATABASES LIKE "{db_name}"') return cur.rowcount > 0

The LIKE clause takes a string parameter so the thing that follows it must be a string. That means it must be enclosed in single or double quotes.

Note that SQL commands are case-insensitive so this is the same as SHOW DATABASES, show databases, and Show Databases are all the same to SQL. By custom, however, SQL statements are usually written in ALL CALLS. That's mostly for historic reasons (SQL was invented in the 1970s before lower case letters existed), but it also helps SQL statements stand out from other code like your Python program.

In this example, the LIKE statement does not include any wildcards, so it returns only exact matches. For example, if the statement is SHOW DATABASES LIKE "Library_Db", the statement returns information about the Library_Db database and nothing else.

The function checks the cursor's rowcount and returns True if the statement returned any information.

Listing Databases

def show_database_information(cur, db_name): '''List some database information.''' cur.execute('SHOW DATABASES') print('All Databases:') for database in cur: # Each entry is a tuple containing one item: the database name. print(f' {database[0]}') # See if the database exists. exists = database_exists(cur, db_name) print(f'{db_name} exists: {exists}') # List databases like db_name. # No wild cards so this only lists the one database (if it exists). print(f'{db_name} Databases:') cur.execute(f'SHOW DATABASES LIKE "{db_name}"') for database in cur: print(f' {database[0]}') # See what database names contain "schema". ''' % Represents zero or more characters _ Represents a single character [] Represents any single character within the brackets ^ Represents any character not in the brackets - Represents any single character within the specified range {} Represents any escaped character ''' cur.execute('SHOW DATABASES LIKE "%schema%"') print('Schema Databases:') for database in cur: print(f' {database[0]}') print('**********')

Next, the code calls the database_exists function described earlier to see if the databases exists. The code then uses a statement similar to the one used in that function to list the database whose name is in db_name.

The code then uses a SHOW DATABASES statement with wildcards to list information about databases that have the word "schema" in their names. Note that the database names are in lowercase and this query also works with lowercase. That means if you check for databases that have "SCHEMA" in their names, you'll get the same results.

Also note that a multi-line comment describes the wildcard characters that SQL understands.

The function lists the matching databases and then prints some asterisks to separate the output from later output.

The following text shows this function's output when the Library_Db database exists.

All Databases: accounts_db information_schema library_db mysql performance_schema sys Library_Db exists: True Library_Db Databases: library_db Schema Databases: information_schema performance_schema **********

Dropping a Database

def drop_database(cur, db_name): '''Drop the database.''' # Don't omit IF EXISTS unless you are sure it exists. cur.execute(f'DROP DATABASE IF EXISTS {db_name}')

If you omit the IF EXISTS part, the statement raises an exception if the database doesn't exist.

Creating a Database

def create_database(cur, db_name): '''Create the database.''' # Create the database. # Don't omit IF NOT EXISTS unless you're sure the database doesn't exist. cur.execute(f'CREATE DATABASE IF NOT EXISTS {db_name}')

Calling the Functions

# Main program. root_password = 'my_super_secret_password' db_name = 'Library_Db' # Connect to MariaDB. try: conn = mariadb.connect( user='root', password=root_password, host='localhost', port=3306 # Default MariaDB port ) except mariadb.Error as e: print(f'Error connecting to MariaDB: {e}') else: try: # Create a cursor. cur = conn.cursor() # Drop the LibraryDb database. drop_database(cur, db_name) # Show information about current databases. show_database_information(cur, db_name) # Create the LibraryDb database. create_database(cur, db_name) # Show information about current databases. show_database_information(cur, db_name) except mariadb.Error as e: print(f'MariaDB error: {e}') finally: # Close the cursor and connection. cur.close() conn.close()

Next, the program calls the drop_database function to drop the Library_Db database if it exists. It then calls show_database_information to show what things look like without that database.

The program calls create_database to create the database and then calls show_database_information again to show the results when the database exists.

Here's the example's complete output.

All Databases: accounts_db information_schema mysql performance_schema sys Library_Db exists: False Library_Db Databases: Schema Databases: information_schema performance_schema ********** All Databases: accounts_db information_schema library_db mysql performance_schema sys Library_Db exists: True Library_Db Databases: library_db Schema Databases: information_schema performance_schema **********

Conclusion

In my next MariaDB post, I'll show you how you can create database tables in Python.

For a lot more information about database design and implementation, see my book Beginning Database Design Solutions, Second Edition.