The MySQL database engine

Though we can use SQLite for large databases, MySQL is generally preferred. In addition to being scalable for large databases, MySQL is also useful where data security is paramount. Before using MySQL, you will need to install the Python MySQL connector. There are many possible Python MySQL connectors such as, MySQLdb, PyMySQL, and MySQL; we will use mysql-connector-python.

In all three, after making a connection using the connect method, we define the cursor element and use the execute method to run different SQL queries. To install MySQL, we use the following:

pip install mysql-connector-python 

1. Now that the Python MySQL connector is installed, we can start a connection with the SQL server. Replace the host, user, and password configurations with your SQL server configuration:

import mysql.connector 
connection = mysql.connector.connect(host="127.0.0.1", # your host 
        user="root", # username
        password="**********" ) # password

1. Let's check existing databases in the server and list them. To do this, we use the cursor method:

mycursor = connection.cursor()
mycursor.execute("SHOW DATABASES")
for x in mycursor:
    print(x)

1. We can access one of the existing databases. Let's list the tables in one of the databases:

connection = mysql.connector.connect(host="127.0.0.1", # your host 
user="root", # username
password="**********" ,  #replace with your password
database = 'mysql')
mycursor = connection.cursor()
mycursor.execute("SHOW TABLES")
for x in mycursor:
    print(x)