MySQL is a very popular and open source database server. Python is an old but lately discovered language. In this tutorial, we will look at how to use this popular tech in our applications in order to run SQL queries.
Install Python MySQL Connector
In order to connect the MySQL database from Python application, we need some libraries. There are different libraries that support running SQL queries on MySQL but we will stick with mysql-connector-python3
.
Fedora, CentOS, RHEL
We can install the mysql-connector-python3 library with the following command. This requires root privileges.
$ yum install mysql-connector-python3
Debian, Ubuntu,Mint
We can install the mysql-connector-python3 library with the following command. This requires root privileges.
$ apt install mysql-connector-python3
Pip
Pip can be used to install mysql connector library.
$ pip3 install mysql-connector-python
Load MySQL Library
In order to use the MySQL library, we need to import it into our application. We will use import
statement to import library which is named mysql.connector
.
#!/bin/python3
import mysql.connector
Connect MySQL Database with Username and Password
Now we can connect to the database. While connecting MySQL database we to provide following parameters to the mysql.connector.connect
function
user
is the username to authenticatepassword
is the password of the userhost
the database server hostname or IP addressdatabase
is optional which provides the database name
#!/bin/python3
import mysql.connector
dbcon = mysql.connector.connect(user='root',password='',host='localhost', database='poftut')
dbcon.close()
Run and Execute SQL Query
In order to run the SQL query, we need to create a cursor that is like a SQL query window in GUI SQL tools. We will use dbcon.cursor()
and then use created cursor
objects execute
function by providing the SQL.
#!/bin/python3
import mysql.connector
dbcon = mysql.connector.connect(user='root',password='S3cR.t',host='localhost')
cur = dbcon.cursor()
cur.execute("SELET * FROM articles")
Print and List Returned Data
After executing the query the results will be saved to the cursor object named cur
. We can get and list returned information from cur
object. Provided data is stored in a list format. So we can use loops to iterate and print.
#!/bin/python3
import mysql.connector
dbcon = mysql.connector.connect(user='root',password='',host='localhost')
cur = dbcon.cursor()
cur.execute("SELET * FROM articles")
for (id, name) in cur:
print("{}, {}".format(id, name))
Close MySQL Connection
One of the most important part of database programming is using sources very strictly. In order to prevent performance problems we need to close the connection to the MySQL database after finishing job. We will use close()
function of connection object.
#!/bin/python3
import mysql.connector
dbcon = mysql.connector.connect(user='root',password='',host='localhost')
cur = dbcon.cursor()
cur.execute("SELET * FROM articles")
for (id, name) in cur:
print("{}, {}".format(id, name))
dbcon.close()