Installing MySQL Server

The official documentation details the recommended way to download and install MySQL server. You’ll find instructions for all popular operating systems, including Windows , macOS , Solaris , Linux , and many more.

For Windows, the best way is to download MySQL Installer and let it take care of the entire process. The installation manager also helps you configure the security settings of the MySQL server. On the Accounts and Roles page, you need to enter a password for the root (admin) account and also optionally add other users with varying privileges:

While you must specify credentials for the root account during setup, you can modify these settings later on.

Note: Remember the hostname, username, and password as these will be required to establish a connection with the MySQL server later on.

Although you only need the MySQL server for this tutorial, you can also set up other helpful tools like MySQL Workbench using these installers. If you don’t want to install MySQL directly in your operating system, then deploying MySQL on Linux with Docker is a convenient alternative.

Installing MySQL Connector/Python

A database driver is a piece of software that allows an application to connect and interact with a database system. Programming languages like Python need a special driver before they can speak to a database from a specific vendor.

These drivers are typically obtained as third-party modules. The Python Database API (DB-API) defines the standard interface with which all Python database drivers must comply. These details are documented in PEP 249 . All Python database drivers, such as sqlite3 for SQLite, psycopg for PostgreSQL, and MySQL Connector/Python for MySQL, follow these implementation rules.

Note: MySQL’s official documentation uses the term connector instead of driver . Technically, connectors are associated only with connecting to a database, not interacting with it. However, the term is often used for the entire database access module comprising the connector and the driver.

To maintain consistency with the documentation, you’ll see the term connector whenever MySQL is mentioned.

Many popular programming languages have their own database API. For example, Java has the Java Database Connectivity (JDBC) API. If you need to connect a Java application to a MySQL database, then you need to use the MySQL JDBC connector , which follows the JDBC API.

Similarly, in Python you need to install a Python MySQL connector to interact with a MySQL database. Many packages follow the DB-API standards, but the most popular among them is MySQL Connector/Python . You can get it with pip :

pip installs the connector as a third-party module in the currently active virtual environment. It’s recommended that you set up an isolated virtual environment for the project along with all the dependencies.

To test if the installation was successful, type the following command on your Python terminal :

If the above code executes with no errors, then mysql.connector is installed and ready to use. If you encounter any errors, then make sure you’re in the correct virtual environment and you’re using the right Python interpreter.

Make sure that you’re installing the correct mysql-connector-python package, which is a pure-Python implementation. Beware of similarly named but now depreciated connectors like mysql-connector .

Establishing a Connection

The first step in interacting with a MySQL server is to establish a connection. To do this, you need connect() from the mysql.connector module. This function takes in parameters like host , user , and password and returns a MySQLConnection object. You can receive these credentials as input from the user and pass them to connect() :

The code above uses the entered login credentials to establish a connection with your MySQL server. In return, you get a MySQLConnection object, which is stored in the connection variable. From now on, you’ll use this variable to access your MySQL server.

There are several important things to notice in the code above:

You should always deal with the exceptions that might be raised while establishing a connection to the MySQL server. This is why you use a try … except block to catch and print any exceptions that you might encounter.

You should always close the connection after you’re done accessing the database. Leaving unused open connections can lead to several unexpected errors and performance issues. The above code takes advantage of a context manager using with , which abstracts away the connection cleanup process.

You should never hard-code your login credentials , that is, your username and password, directly in a Python script. This is a bad practice for deployment and poses a serious security threat . The code above prompts the user for login credentials. It uses the built-in getpass module to hide the password. While this is better than hard-coding, there are other, more secure ways to store sensitive information, like using environment variables .

You’ve now established a connection between your program and your MySQL server, but you still need to either create a new database or connect to an existing database inside the server.

Creating a New Database

To execute a SQL query in Python, you’ll need to use a cursor , which abstracts away the access to database records. MySQL Connector/Python provides you with the MySQLCursor class, which instantiates objects that can execute MySQL queries in Python. An instance of the MySQLCursor class is also called a cursor .

cursor objects make use of a MySQLConnection object to interact with your MySQL server. To create a cursor , use the .cursor() method of your connection variable:

The above code gives you an instance of the MySQLCursor class.

A query that needs to be executed is sent to cursor.execute() in string format. In this particular occasion, you’ll send the CREATE DATABASE query to cursor.execute() :

After executing of the code above, you’ll have a new database called online_movie_rating in your MySQL server.

The CREATE DATABASE query is stored as a string in the create_db_query variable and then passed to cursor.execute() for execution. The code uses a context manager with the cursor object to handle the cleanup process.

You might receive an error here if a database with the same name already exists in your server. To confirm this, you can display the name of all databases in your server. Using the same MySQLConnection object from earlier, execute the SHOW DATABASES statement :

>>> show_db_query = "SHOW DATABASES"
>>> with connection.cursor() as cursor:
...     cursor.execute(show_db_query)
...     for db in cursor:
...         print(db)
('information_schema',)
('mysql',)
('online_movie_rating',)
('performance_schema',)
('sys',)
The above code prints the names of all the databases currently in your MySQL server. The SHOW DATABASES command also outputs some databases that you didn’t create in your server, like information_schema, performance_schema, and so on. These databases are generated automatically by the MySQL server and provide access to a variety of database metadata and MySQL server settings.   
You created a new database in this section by executing the CREATE DATABASE statement. In the next section, you’ll see how to connect to a database that already exists.
 
 
 




    
 Remove ads

Connecting to an Existing Database