python - connecting to MySQL

If you do Python programming, sooner or later you're going to need to access some databases. This tutorial walks through how you can access MySQL from your Python application.

To connect to MySQL from Python, we'll use the mysql.connector Python module. Use pip to install it for your Python environment :

[skszeto@deuce ~]$ sudo pip install mysql-connector
Collecting mysql-connector
Installing collected packages: mysql-connector
Successfully installed mysql-connector-2.1.6

The following Python code demonstrates how to connect to MySQL DB using the mysql.connector driver :

import mysql.connector

def connect(host, username, password, database):
    try:
        c = mysql.connector.connect(host = host,
        user = username,
        password = password,
        db = database,
        )
        return c
    except Exception, e:
        print ("Exception is %s") % (e)
    finally:
        #closing database connection.
        if(mydb.is_connected()):
            mydb.close()
            print("Closing MySQL connection.")

mydb = connect(host, username, password, database)
mycursor = mydb.cursor()

In the above example, we define a function called "connect". In the function, it uses the connect() constructor to create a connection to the MySQL server using the following connection parameters :

host = (DNS name or IP address of the MySQL server; it'll connect to the default MySQL port 3306)
user = (DB username)
password = (password of the DB username)
db = (the database to connect to)

In the example, it assigns the connection object to the variable mydb, and creates a Cursor object mycursor. The Cursor object allows us to run the execute() method, which in turn enables us to run SQL statements such as DELETE, INSERT, SELECT, etc.

We can then run the following query to access the database table marketcaps :

market_date = '2018-11-29'
number_of_stocks = 13

sql = """select symbol, marketcap from marketcaps where market_date = '%s' order by marketcap desc limit %s""" % (market_date, number_of_stocks)

mycursor.execute(sql)
records = mycursor.fetchall()
mycursor.close()

The above query will search the table marketcaps, and return records that have the market_date field equals to '2018-11-29'. It'll limit the output to the top 13 records in descending order based on the marketcap field.

Following is the complete sample code to connect and access my MySQL database :

import mysql.connector
import sys

def connect(host, emanresu, dwssap, database):
    try:
        c = mysql.connector.connect(host = host,
        user = emanresu,
        password = dwssap,
        db = database,
        )
        return c
    except Exception, e:
        print ("Exception is %s") % (e)

def get_data(market_date, number_of_stocks):
    try:
        sql = """select symbol, marketcap from marketcaps where market_date = '%s' order by marketcap desc limit %s""" % (market_date, number_of_stocks)
        mycursor.execute(sql)
        records = mycursor.fetchall()
        for row in records:
          print ("%s\t has Market Cap = %s") % (row[0], '${:,.0f}'.format(row[1]))
        mycursor.close()

    except Exception, e:
        print ("Exception is %s") % (e)
    finally:
        #closing database connection.
        if(mydb.is_connected()):
            mydb.close()
            print("Closing MySQL connection.")

market_date = sys.argv[1]
number_of_stocks = sys.argv[2]

mydb = connect('localhost', 'deuce', '---------', 'stock_market')
mycursor = mydb.cursor()

print ("market_date is %s, number_of_stocks is %s") % (market_date, number_of_stocks)
get_data(market_date, number_of_stocks)

[skszeto@deuce ~]$ testdb.py 2018-11-29 13
market_date is 2018-11-29, number_of_stocks is 13
AAPL     has Market Cap = $852,036,210,900
MSFT     has Market Cap = $845,842,542,520
AMZN     has Market Cap = $818,323,226,762
GOOGL    has Market Cap = $761,243,172,091
GOOG     has Market Cap = $756,875,645,623
BABA     has Market Cap = $401,941,195,864
FB       has Market Cap = $398,536,866,032
JNJ      has Market Cap = $391,166,486,779
JPM      has Market Cap = $365,994,704,394
XOM      has Market Cap = $334,724,794,860
V        has Market Cap = $306,960,427,419
WMT      has Market Cap = $284,936,586,899
BRK.B    has Market Cap = $284,009,792,039
Closing MySQL connection.
[skszeto@deuce ~]$