MySQL is a complex database management system. In this blog post, we’ll introduce you to MySQL through some simple programs.
MySQL provides a whole range of data types, including the following:
- VARCHAR for character strings
- INT for integers
- DOUBLE for numbers with decimal places
- DATE for dates
XAMPP and Connector/Python
A local MySQL database server is required to test the programs. This local server can be found in the XAMPP package, which is preconfigured and easy to install.
The Connector/Python driver provides an interface between Python and MySQL. You can install it with the following commands:
- On Windows and macOS: pip install mysql-connector-python
- On Ubuntu Linux: sudo apt install python3-mysql.connector
Note the different name used in the Ubuntu Linux command.
Database, Table, and Data Records
Similar to SQLite, our program creates a MySQL database first and then a table with a unique index. Finally, three data records will be created in the table.
import sys, mysql.connector
try:
connection = mysql.connector.connect \
(host = "localhost", user = "root", passwd = "")
except:
print("No connection to the server")
sys.exit(0)
cursor = connection.cursor()
cursor.execute("CREATE DATABASE IF NOT EXISTS company")
connection.commit()
cursor.execute("USE company")
connection.commit()
cursor.execute("CREATE TABLE IF NOT EXISTS people"
"(name VARCHAR(30), firstname VARCHAR(25),"
"personnelnumber INT(11), salary DOUBLE, "
"birthday DATE, PRIMARY KEY (personnelnumber))"
"ENGINE=InnoDB DEFAULT CHARSET=UTF8")
connection.commit()
try:
cursor.execute("INSERT INTO people VALUES('Mayer', " \
"'John', 6714, 3500, '1962-03-15')")
connection.commit()
cursor.execute("INSERT INTO people VALUES('Smith', " \
"'Peter', 81343, 3750, '1958-04-12')")
connection.commit()
cursor.execute("INSERT INTO people VALUES('Mertins', " \
"'Judith', 2297, 3621.5, '1959-12-30')")
connection.commit()
except:
print("Error during creation")
cursor.close()
connection.close()
After importing the mysql.connector module, a connection to the database server is established using the connect() function. The name of the server (in this case, localhost); the name of the user (in this case, root); and the password (in this case, no password) are specified. If the MySQL database server is not running, the connection will fail. The return value of the connect() function is a connection object. The database can be accessed via this connection.
The cursor() method is then used to create a cursor object. This cursor can be used to send SQL commands to the database and receive the results.
The execute() method is used to send SQL commands. The SQL command CREATE DATABASE company creates a new database named company. The IF NOT EXISTS addition ensures that the database is only created if it does not exist yet.
Calling the commit() method of the connection object causes the SQL statement to be executed immediately.
The USE company SQL command is used to select the company database after it has been created.
The people table is created with five fields. The name and firstname fields are of type VARCHAR. A maximum length of 30 and 25 characters is selected. The personnelnumber field is of type INT. The size 11 is the default value for the type INT. The salary field is of type DOUBLE for numbers with decimal places. The birthday field is of type DATE for dates. The IF NOT EXISTS addition also means that the table will only get created if it does not exist yet.
A primary key is defined in the personnelnumber field. This key ensures that no two entries can have the same personnel number.
The INSERT INTO SQL command is used to create three data records. The data is entered in the order of the fields. Strings are enclosed in single quotation marks. Decimal places are separated by a decimal point. A date is entered in the format YYYY-MM-DD and also within single quotation marks.
A second call of the same program would lead to an error message, as a primary key is defined in the personnelnumber field. No entry can be created that has the same personnel number as an existing data record.
At the end of these actions, the cursor object and the connection object are closed again using the close() method.
The program has no output. XAMPP contains the phpMyAdmin program for managing the MySQL server databases. You can use this program to verify the existence of the new database, the table, and the data records. Alternatively, you can call the program, which we’ll describe next.
Displaying Data
Our next program displays all the data records in the table with all their contents.
import sys,
mysql.connector
try:
connection = mysql.connector.connect(host = "localhost", \
user = "root", passwd = "", db = "company")
except:
print("No connection to the server or no database")
sys.exit(0)
cursor = connection.cursor()
cursor.execute("SELECT * FROM people")
result = cursor.fetchall()
cursor.close()
connection.close()
for dr in result:
for field in dr:
print(f"{field} ", end="")
print()
The program generates the following output:
Mertins Judith 2297 3621.5 1959-12-30
Mayer John 6714 3500.0 1962-03-15
Smith Peter 81343 3750.0 1958-04-12
The connect() function is called with a fourth parameter. Not only does this function establish a connection to the database server but also to the database on the server.
All data records are queried using the SELECT SQL command. As this command is not an action query, only a selection query, the commit() method does not need to be called at this point.
The fetchall() method of the cursor object returns a list containing the result of the query. The individual data records, with the values of their fields, are output.
Editor’s note: This post has been adapted from a section of the book Getting Started with Python by Thomas Theis.
Comments