Manipulate SQL in Python | How to use MySQL from Python

Introduction

What i used: Anaconda command prompt, Sublime Text Editor, SO: Windows 10, SQL command line

Creating a virtual environment

(I ran the following in the Anaconda command prompt 🙂

conda create – name myenv (here myenv is the name of your virtual environment)

To activate this environment, use-

conda activate myenv 
##This will create a directory namely myenv

Note What (base) change to (myenv) when you activate your environment.

In the same way, to disable this environment, use

conda deactivate myenv

Installing required packages:

Run this command:

pip install mysql-connector-python

The required package is installed.

Connect to MySQL

import mysql.connector

db = mysql.connector.connect(host=’localhost’,
user=’yourusername’,
passwd=’yourpass’,
database=’fifa19', 
port=’3307',
auth_plugin=’mysql_native_password’)

print(db)

Here,
1. write your MySQL username and password in the username and password arguments.
2. The database will have the name of the database you want to use.
3. MySQL port is 3307. If your port is 3306, you don't need to specify it explicitly.
4. auth_plugin = ‘mysql_native_password’: this argument is specified to avoid authentication errors.
5. pprint

This creates a MySQL.connector.connection object

View database data

We use the cursor method to create a cursor object that is used to execute statements to communicate with MySQL databases.

The following code is written in the text editor.

crs = db.cursor()
crs.execute(“select * from players limit 10”)
result = crs.fetchall()

pprint(result)

Here,
1. crs is the name of my cursor object.
2. run() accepts SQL queries as parameter and executes the given query.
3. The result variable stores the result set of fetchall () method.
4. fetchall () method returns the query result set as tuples.
5. pprint () prints the output in a more formatted and readable way. [You can import pprint with: from pprint import pprint]

Save and run the file via command prompt. The output will look like the following:

Creating table

Write the following code in the text editor:

cmd=”create table contacts(Name Varchar(255),PhoneNo int(12))”
print(cmd)
crs.execute(cmd)

Note that we run all queries on the cursor object.

Insert query

The code for the insert command looks like this:

insert_command= “insert into contacts(Name,PhoneNo) values(%s,%s)”
values=(“Sejal”,987654321)

crs.execute(insert_command,values)

db.commit()

Here, db.commit is required () run every time we make changes to the database. This method is used to ensure that changes to the database are consistent.

Run the file and you can see that the data has been incorporated into your table. You can check this using the SQL command line.

Search query

The code for the search records looks like this:

crs.execute(“select Name from players where Nationality=’United States’”)
result = crs.fetchall()

pprint(result)

Here, we are trying to find names of players whose nationality is the United States.

The output will look like the following:

Delete query

The code to delete records looks like this:

crs.execute(“delete from contacts”)
db.commit()

Remember, usamos db.commit () to make sure changes are reflected in the database.

After running the file, you can see that the records have been removed from the database.

I hope you have learned something in a relatively easier way about how to manipulate SQL in Python!!