This is the second part in a tutorial series showcasing how to program database-driven applications using Python and MariaDB. In the first part of this series, we learned how to setup and configure a MariaDB database and installed a demo database table. you can read that database programming tutorial by visiting: Python Database Programming with MySQL/MariaDB.
In most database-enabled scripting, unless something like Trusted Connections offered by SQL Express, or unix_socket authentication offered by MariaDB, is used, then it is required that the unencrypted credentials to access the database be embedded somewhere in the code that is accessible by that code. Some examples of this include, but are not limited to:
- Connection Strings or credentials in the code itself, or in a file that is read or included by such code.
- For web applications, a file that is read by the code, but is outside of the webroot directory that can be browsed publicly.
These concerns hold true no matter what scripting language is used. That being stated, the demonstrations below will include the username and password in the code directly. Properly securing these credentials, or using alternative means of authentication which do not require the use of unencrypted credentials, are beyond the scope of an introductory tutorial.
Read: Key Differences Between T-SQL and SQL
How to Install mysql.connector
Python uses a module named mysql.connector to enable communications to MySQL and MariaDB databases. This module can be added to Python via the pip3 command. However, the version of Ubuntu Server used for these articles does not have this utility installed by default. It will be necessary to install it. This can be done with the command:
$ sudo apt install python3-pip
As was the case with installing MariaDB, it may be necessary to use a different package manager depending on the Linux version being used.
Note: If multiple versions of Python are installed, such as both Python 2 and Python 3, then it is imperative to make sure that the correct pip command be used.
To install mysql.connector via pip3, invoke the command:
$ pip3 install mysql.connector
Something similar to the following output will appear upon successful completion of the mysql.connector module:
Figure 1 – Successful installation of mysql.connector
The code example below will test if it is possible to connect to the database. If the code fails, an unhandled exception will be thrown and the code will exit. Common causes of a connection failure include:
- Parse errors in the connection function.
- Typos in the credentials
- Failing to escape special characters in the credentials (e.g., \” instead of “)
- The MariaDB or MySQL Server rejects connections on localhost, but allows them on 127.0.0.1, or vice versa.
- Using valid credentials, but attempting to connect from a disallowed source, such as a remote server.
Exception handling is better presented in a more intermediate-level article. If the code is successful, there will be no notification that the database connection was made, and 15 randomly generated “bad” band names and some sample albums will be generated.
# bad-band-name-maker.py
import sys
import random
import mysql.connector
part1 = ["The", "Uncooked", "Appealing", "Larger than Life", "Drooping", "Unwell", "Atrocious", "Glossy", "Barrage", "Unlawful"]
part2 = ["Defeated", "Hi-Fi", "Extraterrestrial", "Adumbration", "Limpid", "Looptid", "Cromulent", "Unsettled", "Soot", "Twinkle"]
part3 = ["Brain", "Segment", "Audio", "Legitimate Business", "Mentality", "Sound", "Canticle", "Monsoon", "Preserves", "Hangout"]
part4 = ["Cougar", "Lion", "Lynx", "Ocelot", "Puma", "Jaguar", "Panther"]
part5 = ["Fodder", "Ersatz Goods", "Leftovers", "Infant Formula", "Mush", "Smoothie", "Milkshakes"]
def main(argv):
# Connect to the RazorDemo database.
conn = mysql.connector.connect(user='rd_user', password='myPW1234%', host='127.0.0.1', database='RazorDemo')
# Generate 15 unique bad band names:
previousNames = ""
nameCount = 0
while nameCount < 16:
rand1 = random.randrange(0, 9)
rand2 = random.randrange(0, 9)
rand3 = random.randrange(0, 9)
badName = part1[rand1] + ' ' + part2[rand2] + ' ' + part3[rand3]
# A crude but effective way of ensuring uniqueness, although there is no unique constraint on the artist name in the
# database.
# This prepends and appends bars to both the list of previously used names and the current name. If the current name is
# new, it will not be in that string.
if -1 == ("|" + previousNames + "|").find("|" + badName + "|"):
print ("Band name [" + str(1 + nameCount) + "] is [" + badName + "]")
for y in range(1, 3):
# There will be no check for duplicated album names. There are more than a few acts which have reused album
# names.
rand4 = random.randrange(0, len(part4))
rand5 = random.randrange(0, len(part5))
albumName = part4[rand4] + " " + part5[rand5]
print ("\tAlbum [" + albumName + "]")
# Creates a bar-delimited list of previously used names.
if "" == previousNames:
previousNames = badName
else:
previousNames = previousNames + "|" + badName
nameCount = 1 + nameCount
# Close the Connection
conn.close()
return 0
if __name__ == "__main__":
main(sys.argv[1:])
Listing 2 - Testing database connectivity and basic functionality.
This code will give output similar to what is shown below:
Figure 2 – Output of basic connectivity application
Read: Using the Command Line to Write SQL Data
MariaDB Security Setup
All application development for SQL-oriented databases must take care to ensure that any SQL code that is generated within the application is properly secured. This often results in sacrificing efficiency for security. While this can be an annoying tradeoff, it contributes to a more secure application, and no developer wants to be in a position of having a compromised database (or worse) as a result of improperly secured application code.
Like any other SQL-oriented database connector, mysql.connector provides built-in security features, which must be used to properly sanitize anything that is executed by the database. The mysql.connector module provides a mechanism for creating parameterized statements, also referred to as prepared statements, for the purposes of securely executing SQL statements while ensuring that no unauthorized code is passed into the server. A parameterized statement is an SQL statement in a string that contains placeholders for input. The application code then replaces these placeholders with escaped versions of these strings, so that the statement can be executed safely.
While it is often tempting, especially as a beginning programmer, to simply create strings with SQL statements and pass these directly into the database, it is a very bad programming practice. If data is not sanitized, then it is possible for a malicious user to execute arbitrary SQL code against the application’s database, and if the MariaDB user account is not properly restricted, then other databases on the server may be compromised as well. This kind of attack on a database application is known as a SQL Injection Attack. More information on SQL Injection attacks can be read in Python Database Programming with SQL Express for Beginners.
Note: never under any circumstances pass user-created inputs directly to a database. Always check the input to make sure it will not break database functionality or cause a security problem by way of a SQL Injection attack.
Properly INSERTing Data in MariaDB
Going back to the statements executed in the “Creating Tables” section of the previous part of this tutorial, it can be seen that any album record that is inserted will need to have the record identifier of its respective artist. This record ID isn’t just any random column in the table, it is the column identified by the primary key clause in the create table… statement. In the case of the Artists table, this is the rcdid column.
The mysql.connector module provides two mechanisms for executing parameterized SQL statements. The .execute() method allows for the execution of a single such statement, but the .executemany() method allows for multiple iterations of the same statement to be executed, albeit with different values for the parameters in the statement. The demonstration below will make use of both methods. The .execute() method will be used for the artist data because it enables the record identifiers for each artist to be recovered and then used to create the insert statement used for the albums. On the other hand, there is no need to recover the record identifiers for each of the albums, so the .executemany() method will work better for those.
The code below inserts each artist into the Artists table, retrieves the record ID of each newly created record, and then inserts that record ID into the insert statement for each album corresponding to the artist.
# bad-band-name-maker2.py
import sys
import random
import mysql.connector
part1 = ["The", "Uncooked", "Appealing", "Larger than Life", "Drooping", "Unwell", "Atrocious", "Glossy", "Barrage", "Unlawful"]
part2 = ["Defeated", "Hi-Fi", "Extraterrestrial", "Adumbration", "Limpid", "Looptid", "Cromulent", "Unsettled", "Soot", "Twinkle"]
part3 = ["Brain", "Segment", "Audio", "Legitimate Business", "Mentality", "Sound", "Canticle", "Monsoon", "Preserves", "Hangout"]
part4 = ["Cougar", "Lion", "Lynx", "Ocelot", "Puma", "Jaguar", "Panther"]
part5 = ["Fodder", "Ersatz Goods", "Leftovers", "Infant Formula", "Mush", "Smoothie", "Milkshakes"]
def main(argv):
# Connect to the RazorDemo database.
conn = mysql.connector.connect(user='rd_user', password='myPW1234%', host='127.0.0.1', database='RazorDemo')
# Generate 15 unique bad band names:
previousNames = ""
nameCount = 0
albumTuples = []
cursor = conn.cursor()
while nameCount < 16:
rand1 = random.randrange(0, 9)
rand2 = random.randrange(0, 9)
rand3 = random.randrange(0, 9)
badName = part1[rand1] + ' ' + part2[rand2] + ' ' + part3[rand3]
# A crude but effective way of ensuring uniqueness, although there is no unique constraint on the artist name in the
# database.
# This prepends and appends bars to both the list of previously used names and the current name. If the current name is
# new, it will not be in that string.
if -1 == ("|" + previousNames + "|").find("|" + badName + "|"):
lastInsertID = -1
print ("Band name [" + str(1 + nameCount) + "] is [" + badName + "]")
sql1 = "insert into Artists (artist_name) values(%s)"
values1 = [badName]
cursor.execute(sql1, values1)
conn.commit()
lastInsertID = cursor.lastrowid
for y in range(1, 3):
# There will be no check for duplicated album names. There are more than a few acts which have reused album
# names. However, it is possible for the same "random" album name to be generated and used for multiple
# artists.
rand4 = random.randrange(0, len(part4))
rand5 = random.randrange(0, len(part5))
albumName = part4[rand4] + " " + part5[rand5]
#print ("\tAlbum [" + albumName + "]")
albumTuples.append((lastInsertID, albumName))
# Since we do not care about the record IDs for rows in the Albums table, we can use .executemany()
# Creates a bar-delimited list of previously used names.
if "" == previousNames:
previousNames = badName
else:
previousNames = previousNames + "|" + badName
nameCount = 1 + nameCount
# Loop through the album tuples and insert these all at once.
print (albumTuples)
sql2 = "insert into Albums (artist_id, album_name) values(%s, %s)"
cursor.executemany(sql2, albumTuples)
conn.commit()
cursor.close()
# Close the Connection
conn.close()
return 0
if __name__ == "__main__":
main(sys.argv[1:])
Listing 3 - Inserting randomly generated data
Before reviewing the output of this code, it is important to note a few practical problems that can arise in any coding for any SQL-oriented database application. Ideally, a good programming practice involving such code is to reduce, as much as possible, the number of interactions with the database, because each individual commit to the database incurs an overhead and that overhead can add up quickly if multiple nested SQL statements are executed in a loop. Using the .execute() method above to insert artist records into a database within a loop might be useful for an introductory demonstration, but in practical coding, it can significantly slow down application performance.
Using the .executemany() method with the album records, however, tells a different story. In this case, multiple tuples are passed into a single commit action. This incurs significantly less overhead than executing two insert statements within the for y… loop above. A tuple refers to a specific instance of data within a larger set of data. A tuple can have one or more individual data elements:
Figure 3 – The output of the above code, with tuples explained
If a significant number of statements need to be executed in a loop or similar construct, then consider using stored procedures or batches. However, these present their own coding challenges that are beyond the scope of an introductory MariaDB tutorial.
Note: table names in Linux versions of MariaDB are case-sensitive! This may be a shock to developers who are more familiar with SQL Server or SQL Express. But weirdly, column names are not case-sensitive. The best way to address this inconsistency is to assume that everything in MariaDB is case-sensitive.
The best way to evaluate the outcomes of this code is to look at the tables within the RazorDemo database in the mysql client directly:
Figure – Contents of Artists Table
Figure – Contents of Albums Table
Read: What Are Client Statistics in SQL Server?
SELECTing Data in MariaDB
The code below reads input from standard input and attempts to match it records in the database. Note the use of parameterized statements here for the purposes of preventing SQL Injection attacks:
# bad-band-name-maker3.py
import sys
import mysql.connector
def main(argv):
searchValue = input("Enter something: ")
# Cap the length at something reasonable. The first 20 characters.
searchValue = searchValue[0:20]
# Set the search value to lower case so we can perform case-insensitive matching:
searchValue = searchValue.lower()
# Connect to the RazorDemo database.
conn = mysql.connector.connect(user='rd_user', password='myPW1234%', host='127.0.0.1', database='RazorDemo')
sql1 = ("select a.artist_name, b.album_name from Artists a, Albums b where " +
"b.artist_id=a.rcdid and ( lower(b.album_name) like (concat('%', %s, '%')) or " +
"lower(a.artist_name) like (concat('%', %s, '%')) ) order by a.artist_name, b.album_name;")
values1 = [searchValue, searchValue]
cursor = conn.cursor()
cursor.execute(sql1, values1)
rs1 = cursor.fetchall()
oldAlbum = ""
listings = ""
for row in rs1:
if oldAlbum != row[0]:
listings = listings + row[0] + ":\r\n"
oldAlbum = row[0]
listings = listings + "\t" + row[1] + "\r\n"
# Close the Connection
conn.close()
if "" == listings:
print ("Nothing matched [" + searchValue + "]")
else:
print ("Matches for [" + searchValue + "]")
print (listings)
return 0
if __name__ == "__main__":
main(sys.argv[1:])
Listing 4 - Querying the database
The query in the code above is slightly more complex as it matches on both the album or artist name, and it is important to note that MariaDB, while it still performs a fuzzy match on a string variable that contains a % sign, uses a different string concatenation operator than other databases.
The results below include an example of a crude SQL Injection attack attempt:
Figure 11 – Results of querying the data
Final Thoughts on Python and MariaDB
There is a common misconception out there that simply because a database server is zero-cost or open-source that it cannot deliver the kind of power and scalability needed for more robust software solutions. With regards to MariaDB this for the most part does not apply. It provides data storage for applications like MediaWiki, WordPress, and many other everyday software solutions and it can easily be integrated into any Python-driven application. This combination is well-suited for budget-oriented application developers for whom database servers like SQL Server or Oracle are prohibitively expensive. Hopefully, this article provided the reader with an easy introduction to getting started with both Python and MariaDB.
Read more database programming and database administration tutorials.
