Repository files navigation

In this section, I'll explain how you can connect your MySQL database to Python and query it.To connect MySQL with Python, you will need to have bothMySQL andPython installed on your system.

To access the MySQL database, Python needs a MySQL driver calledMySQL Connector, So you must first install the MySQL connector package on your computer.The MySQL connector package is available on thePython Package Index (PyPI), and it can be easily installed using thepip package manager.

You can do this by running the following command in your terminal:

pip install mysql-connector-python

Import the MySQL connector module into your Python code.This module provides a Python API for connecting to and interacting with a MySQL database.

To import the module, use the following syntax:

importmysql.connector

For purposes of example, we will need a sample database in MySQL that you can connect to.If you don't have a database to do so, follow the below steps:

• First, open a MySQL client tool likeMySQL Workbench or use a terminal.
• Second login to the database using your credentials.
• Finally, run the following command to create a database (for example, company).

CREATEDATABASEcompany;

Create a new MySQLConnection object by calling theconnect() function from the mysql.connector module.This function takes a number of parameters that specify details about the database you want to connect to, such as:

• host: the IP address or hostname of the MySQL server. By default, this is localhost.
• user: the username that you want to use to connect to MySQL.
• password: the password associated with the username.
• database: the name of the database that you want to connect to.

For example:

mydb=mysql.connector.connect(host="localhost",user="<your_username>",password="<your_password>",database="<your_database>")

Once you have successfully established a connection to the database, you can start running SQL queries and performing other operations.

You must create or include a cursor in your python code in order to run MySQL queries.

mycursor=mydb.cursor()

A cursor is a pointer to the result set of a query, and it is used to iterate over the rows of the result set.Cursors are useful for breaking up large result sets into smaller pieces and processing them one row at a time.

By using theCREATE TABLE statement, it creates a new table calledemployee with three columns:

mycursor.execute('''CREATE TABLE employee(    EmployeeID int,    Name varchar(255),    Email varchar(255));''')

Now your table has been created.

By using theINSERT INTO statement to insert some records into your employee table, you just created

mycursor.execute('''  INSERT INTO employee (EmployeeID, Name, Email)    VALUES (101, 'Mark', 'mark@company.com'),           (102, 'Robert', 'robert@company.com'),           (103, 'Spencer', 'spencer@company.com');''')

It's important to remember to save your changes to the database using thecommit() method every time you run the query.

mydb.commit()

This is necessary because MySQL uses a transactional storage engine, which means that changes are not visible to other connections until they are committed.

Now, you have to use theexecute() method of the cursor object to execute aSELECT statement that retrieves all rows from the employee table.Then you have to use a for loop to iterate over the rows returned by the query and print them to the console.

mycursor.execute("SELECT * FROM your_table")forxinmycursor:print(x)

If the above code was executed without errors, you have successfully viewed table records.Now that your cursor is working, you can also run other SQL commands to query the database.

And finally, once you're done, you need to close the cursor and the connection to free up resources and prevent potential issues.

mycursor.close()mydb.close()

You can also get code snippet fromhere.

I amKishlay, and I have written this tutorial, but other people have also helped me with it.If you have any trouble with this tutorial, please tell me about it, and I will make it better.If you like this tutorial and this helped you out, then please give it a star.

If you have any feedback, please reach out to me atcontact.kishlayjeet@gmail.com