◀ Blog

mysql-connector-python

Sample code of mysql-connector-python to connect wiht mysql database.

https://dev.mysql.com/doc/

https://www.w3schools.com/python/python_mysql_getstarted.asp

In [1]:
# conda env py36db
In [5]:
# !pip install mysql-connector-python

Collecting mysql-connector-python
  Downloading mysql_connector_python-8.0.21-cp36-cp36m-win_amd64.whl (811 kB)
Collecting protobuf>=3.0.0
  Downloading protobuf-3.13.0-cp36-cp36m-win_amd64.whl (1.1 MB)
Requirement already satisfied: setuptools in c:\users\jingw\anaconda3\envs\py36db\lib\site-packages (from protobuf>=3.0.0->mysql-connector-python) (49.6.0.post20200925)
Requirement already satisfied: six>=1.9 in c:\users\jingw\anaconda3\envs\py36db\lib\site-packages (from protobuf>=3.0.0->mysql-connector-python) (1.15.0)
Installing collected packages: protobuf, mysql-connector-python
Successfully installed mysql-connector-python-8.0.21 protobuf-3.13.0
In [64]:
# !pip install pandas

Collecting pandas
  Downloading pandas-1.1.2-cp36-cp36m-win_amd64.whl (9.4 MB)
Requirement already satisfied: python-dateutil>=2.7.3 in c:\users\jingw\anaconda3\envs\py36db\lib\site-packages (from pandas) (2.8.1)
Collecting pytz>=2017.2
  Using cached pytz-2020.1-py2.py3-none-any.whl (510 kB)
Collecting numpy>=1.15.4
  Downloading numpy-1.19.2-cp36-cp36m-win_amd64.whl (12.9 MB)
Requirement already satisfied: six>=1.5 in c:\users\jingw\anaconda3\envs\py36db\lib\site-packages (from python-dateutil>=2.7.3->pandas) (1.15.0)
Installing collected packages: pytz, numpy, pandas
Successfully installed numpy-1.19.2 pandas-1.1.2 pytz-2020.1
In [6]:
import mysql.connector
In [85]:
#  Connect to DB

import mysql.connector

mysql_connection = mysql.connector.connect(
    host="localhost",
    user="learnlink",
    password="learnlinkPW01",
    database="mydatabase"
)

print(mysql_connection)

<mysql.connector.connection.MySQLConnection object at 0x000001DC9C23A8D0>

Create Database

In [78]:
cursor = mysql_connection.cursor()
cursor.execute("CREATE DATABASE IF NOT EXISTS mydatabase")

Show Database

In [21]:
cursor = mysql_connection.cursor()
cursor.execute("SHOW DATABASES")
for x in cursor:
  print(x)

('information_schema',)
('learnlink',)
('mydatabase',)
('mysql',)
('performance_schema',)
('sakila',)
('sql_hr',)
('sql_inventory',)
('sql_invoicing',)
('sql_store',)
('sys',)
('world',)

Table

In [79]:
#  Create Table

cursor = mysql_connection.cursor()

cursor.execute("CREATE TABLE IF NOT EXISTS customers ( \
               id INT AUTO_INCREMENT PRIMARY KEY, \
               name VARCHAR(255), \
               address VARCHAR(255) \
              )")
In [25]:
#  Show Table

cursor = mysql_connection.cursor()
cursor.execute("SHOW TABLES")

for x in cursor:
  print(x)

('customers',)
In [36]:
#  Drop Table

cursor = mysql_connection.cursor()
cursor.execute("DROP TABLE IF EXISTS customers")
In [38]:
#  Alter Table

cursor = mysql_connection.cursor()
cursor.execute("ALTER TABLE customers ADD COLUMN id INT AUTO_INCREMENT PRIMARY KEY")

Value

In [41]:
#  Insert Row

cursor = mysql_connection.cursor()
sql = "INSERT INTO customers (name, address) VALUES (%s, %s)"
val = ("John", "Highway 21")
cursor.execute(sql, val)

mysql_connection.commit()

print(cursor.rowcount, "record inserted.")

1 record inserted.
In [42]:
#  Insert Multiple Rows

cursor = mysql_connection.cursor()

sql = "INSERT INTO customers (name, address) VALUES (%s, %s)"
val = [
  ('Peter', 'Lowstreet 4'),
  ('Amy', 'Apple st 652'),
  ('Hannah', 'Mountain 21'),
  ('Michael', 'Valley 345'),
  ('Sandy', 'Ocean blvd 2'),
  ('Betty', 'Green Grass 1'),
  ('Richard', 'Sky st 331'),
  ('Susan', 'One way 98'),
  ('Vicky', 'Yellow Garden 2'),
  ('Ben', 'Park Lane 38'),
  ('William', 'Central st 954'),
  ('Chuck', 'Main Road 989'),
  ('Viola', 'Sideway 1633')
]

cursor.executemany(sql, val)

mydb.commit()

print(cursor.rowcount, "was inserted.")

13 was inserted.

Select From a Table

In [46]:
#  Select All

cursor = mysql_connection.cursor()

cursor.execute("SELECT * FROM customers")

myresult = cursor.fetchall()

for x in myresult:
  print(x)

(1, 'John', 'Highway 21')
(2, 'John', 'Highway 21')
(3, 'John', 'Highway 21')
(4, 'Peter', 'Lowstreet 4')
(5, 'Amy', 'Apple st 652')
(6, 'Hannah', 'Mountain 21')
(7, 'Michael', 'Valley 345')
(8, 'Sandy', 'Ocean blvd 2')
(9, 'Betty', 'Green Grass 1')
(10, 'Richard', 'Sky st 331')
(11, 'Susan', 'One way 98')
(12, 'Vicky', 'Yellow Garden 2')
(13, 'Ben', 'Park Lane 38')
(14, 'William', 'Central st 954')
(15, 'Chuck', 'Main Road 989')
(16, 'Viola', 'Sideway 1633')
In [47]:
#  Selecting Columns

cursor = mysql_connection.cursor()

cursor.execute("SELECT name, address FROM customers")

myresult = cursor.fetchall()

for x in myresult:
  print(x)

('John', 'Highway 21')
('John', 'Highway 21')
('John', 'Highway 21')
('Peter', 'Lowstreet 4')
('Amy', 'Apple st 652')
('Hannah', 'Mountain 21')
('Michael', 'Valley 345')
('Sandy', 'Ocean blvd 2')
('Betty', 'Green Grass 1')
('Richard', 'Sky st 331')
('Susan', 'One way 98')
('Vicky', 'Yellow Garden 2')
('Ben', 'Park Lane 38')
('William', 'Central st 954')
('Chuck', 'Main Road 989')
('Viola', 'Sideway 1633')
In [48]:
#  Fetchone

cursor = mysql_connection.cursor()

cursor.execute("SELECT * FROM customers")

myresult = cursor.fetchone()

print(myresult)

(1, 'John', 'Highway 21')
In [52]:
#  Where

cursor = mysql_connection.cursor()

sql = "SELECT * FROM customers WHERE name ='Ben'"

cursor.execute(sql)

myresult = cursor.fetchall()

for x in myresult:
  print(x)

(13, 'Ben', 'Park Lane 38')
In [53]:
#  Wildcard

cursor = mysql_connection.cursor()

sql = "SELECT * FROM customers WHERE address LIKE '%way%'"

cursor.execute(sql)

myresult = cursor.fetchall()

for x in myresult:
  print(x)

(1, 'John', 'Highway 21')
(2, 'John', 'Highway 21')
(3, 'John', 'Highway 21')
(11, 'Susan', 'One way 98')
(16, 'Viola', 'Sideway 1633')
In [54]:
#  Where

cursor = mysql_connection.cursor()

sql = "SELECT * FROM customers WHERE address = %s"
adr = ("Yellow Garden 2", )

cursor.execute(sql, adr)

myresult = cursor.fetchall()

for x in myresult:
  print(x)

(12, 'Vicky', 'Yellow Garden 2')

Limit

In [60]:
cursor = mysql_connection.cursor()

cursor.execute("SELECT * FROM customers LIMIT 5")

myresult = cursor.fetchall()

for x in myresult:
  print(x)

(4, 'Peter', 'Lowstreet 4')
(5, 'Amy', 'Apple st 652')
(6, 'Hannah', 'Mountain 21')
(7, 'Michael', 'Valley 345')
(8, 'Sandy', 'Ocean blvd 2')
In [61]:
cursor = mysql_connection.cursor()

cursor.execute("SELECT * FROM customers LIMIT 5 OFFSET 2")

myresult = cursor.fetchall()

for x in myresult:
  print(x)

(6, 'Hannah', 'Mountain 21')
(7, 'Michael', 'Valley 345')
(8, 'Sandy', 'Ocean blvd 2')
(9, 'Betty', 'Green Grass 1')
(10, 'Richard', 'Sky st 331')

Order by

In [55]:
cursor = mysql_connection.cursor()

sql = "SELECT * FROM customers ORDER BY name DESC"

cursor.execute(sql)

myresult = cursor.fetchall()

for x in myresult:
  print(x)

(14, 'William', 'Central st 954')
(16, 'Viola', 'Sideway 1633')
(12, 'Vicky', 'Yellow Garden 2')
(11, 'Susan', 'One way 98')
(8, 'Sandy', 'Ocean blvd 2')
(10, 'Richard', 'Sky st 331')
(4, 'Peter', 'Lowstreet 4')
(7, 'Michael', 'Valley 345')
(1, 'John', 'Highway 21')
(2, 'John', 'Highway 21')
(3, 'John', 'Highway 21')
(6, 'Hannah', 'Mountain 21')
(15, 'Chuck', 'Main Road 989')
(9, 'Betty', 'Green Grass 1')
(13, 'Ben', 'Park Lane 38')
(5, 'Amy', 'Apple st 652')

Delete

In [57]:
cursor = mysql_connection.cursor()

sql = "DELETE FROM customers WHERE address = %s"
adr = ("Yellow Garden 2", )

cursor.execute(sql, adr)

mysql_connection.commit()

print(cursor.rowcount, "record(s) deleted")

1 record(s) deleted

Update

In [59]:
cursor = mysql_connection.cursor()

sql = "UPDATE customers SET address = %s WHERE address = %s"
val = ("Valley 345", "Canyon 123")

cursor.execute(sql, val)

mysql_connection.commit()

print(cursor.rowcount, "record(s) affected")

1 record(s) affected

Join

In [63]:
cursor = mysql_connection.cursor()

sql = "SELECT \
  users.name AS user, \
  products.name AS favorite \
  FROM users \
  INNER JOIN products ON users.fav = products.id"

cursor.execute(sql)

myresult = cursor.fetchall()

for x in myresult:
  print(x)
In [ ]:
#  LEFT JOIN

sql = "SELECT \
  users.name AS user, \
  products.name AS favorite \
  FROM users \
  LEFT JOIN products ON users.fav = products.id"
In [ ]:
#  RIGHT JOIN

sql = "SELECT \
  users.name AS user, \
  products.name AS favorite \
  FROM users \
  RIGHT JOIN products ON users.fav = products.id"

mysql.connector + Pandas

In [66]:
import pandas as pd
In [67]:
#  Select All

cursor = mysql_connection.cursor()

cursor.execute("SELECT * FROM customers")

myresult = cursor.fetchall()

for x in myresult:
  print(x)

df = pd.DataFrame(myresult)

print(df)

(4, 'Peter', 'Lowstreet 4')
(5, 'Amy', 'Apple st 652')
(6, 'Hannah', 'Mountain 21')
(7, 'Michael', 'Valley 345')
(8, 'Sandy', 'Ocean blvd 2')
(9, 'Betty', 'Green Grass 1')
(10, 'Richard', 'Sky st 331')
(11, 'Susan', 'One way 98')
(13, 'Ben', 'Park Lane 38')
(14, 'William', 'Central st 954')
(15, 'Chuck', 'Main Road 989')
(16, 'Viola', 'Sideway 1633')
     0        1               2
0    4    Peter     Lowstreet 4
1    5      Amy    Apple st 652
2    6   Hannah     Mountain 21
3    7  Michael      Valley 345
4    8    Sandy    Ocean blvd 2
5    9    Betty   Green Grass 1
6   10  Richard      Sky st 331
7   11    Susan      One way 98
8   13      Ben    Park Lane 38
9   14  William  Central st 954
10  15    Chuck   Main Road 989
11  16    Viola    Sideway 1633
In [83]:
df = pd.read_sql('SELECT * FROM customers', con=mysql_connection)
df
Out[83]:
id name address
0 4 Peter Lowstreet 4
1 5 Amy Apple st 652
2 6 Hannah Mountain 21
3 7 Michael Valley 345
4 8 Sandy Ocean blvd 2
5 9 Betty Green Grass 1
6 10 Richard Sky st 331
7 11 Susan One way 98
8 13 Ben Park Lane 38
9 14 William Central st 954
10 15 Chuck Main Road 989
11 16 Viola Sideway 1633

Function

In [88]:
# Connect to Database with error check

import mysql.connector

def mysql_connection():
    ''' Create connection'''
    
    try:
      mysql_connection = mysql.connector.connect(
            host="localhost",
            user="learnlink",
            password="learnlinkPW01"
      )
    except mysql_connection.connector.Error as err:
      if err.errno == errorcode.ER_ACCESS_DENIED_ERROR:
        print("Something is wrong with your user name or password")
      elif err.errno == errorcode.ER_BAD_DB_ERROR:
        print("Database does not exist")
      else:
        print(err)
    else:
      print(mysql_connection)
    
    return mysql_connection

mysql_connection = mysql_connection()

<mysql.connector.connection.MySQLConnection object at 0x000001DC9C256390>
In [96]:
# Connect to Database with error check

import mysql.connector

def mysql_connection():
    ''' Create connection'''
    
    try:
      mysql_connection = mysql.connector.connect(
          host="localhost",
          user="learnlink",
          password="learnlinkPW01",
          database='employees'
      )
    except mysql_connection.connector.Error as err:
      if err.errno == errorcode.ER_ACCESS_DENIED_ERROR:
        print("Something is wrong with your user name or password")
      elif err.errno == errorcode.ER_BAD_DB_ERROR:
        print("Database does not exist")
      else:
        print(err)
    else:
      print(mysql_connection)
    
    return mysql_connection

mysql_connection = mysql_connection()

<mysql.connector.connection.MySQLConnection object at 0x000001DC9C23ACC0>

Function: Create Database

In [90]:
# Create Database with error check

import mysql.connector

def create_database():
    ''' Create Database'''
    
    DB_NAME = 'employees'
    
    cursor = mysql_connection.cursor()
    
    try:
        cursor.execute(
            "CREATE DATABASE {} DEFAULT CHARACTER SET 'utf8'".format(DB_NAME))
        print("Database {} created successfully.".format(DB_NAME))
        
    except mysql.connector.Error as err:
        print("Failed creating database: {}".format(err))
        exit(1)
        
create_database()

Failed creating database: 1007 (HY000): Can't create database 'employees'; database exists

Function: Create Table

In [91]:
#  Create Table

import mysql.connector
from mysql.connector import errorcode

def create_tables():
    ''' Create Table'''
    
    DB_NAME = 'employees'
    
    cursor = mysql_connection.cursor()

    TABLES = {}
    TABLES['employees'] = (
        "CREATE TABLE `employees` ("
        "  `emp_no` int(11) NOT NULL AUTO_INCREMENT,"
        "  `birth_date` date NOT NULL,"
        "  `first_name` varchar(14) NOT NULL,"
        "  `last_name` varchar(16) NOT NULL,"
        "  `gender` enum('M','F') NOT NULL,"
        "  `hire_date` date NOT NULL,"
        "  PRIMARY KEY (`emp_no`)"
        ") ENGINE=InnoDB")

    TABLES['departments'] = (
        "CREATE TABLE `departments` ("
        "  `dept_no` char(4) NOT NULL,"
        "  `dept_name` varchar(40) NOT NULL,"
        "  PRIMARY KEY (`dept_no`), UNIQUE KEY `dept_name` (`dept_name`)"
        ") ENGINE=InnoDB")

    TABLES['salaries'] = (
        "CREATE TABLE `salaries` ("
        "  `emp_no` int(11) NOT NULL,"
        "  `salary` int(11) NOT NULL,"
        "  `from_date` date NOT NULL,"
        "  `to_date` date NOT NULL,"
        "  PRIMARY KEY (`emp_no`,`from_date`), KEY `emp_no` (`emp_no`),"
        "  CONSTRAINT `salaries_ibfk_1` FOREIGN KEY (`emp_no`) "
        "     REFERENCES `employees` (`emp_no`) ON DELETE CASCADE"
        ") ENGINE=InnoDB")

    TABLES['dept_emp'] = (
        "CREATE TABLE `dept_emp` ("
        "  `emp_no` int(11) NOT NULL,"
        "  `dept_no` char(4) NOT NULL,"
        "  `from_date` date NOT NULL,"
        "  `to_date` date NOT NULL,"
        "  PRIMARY KEY (`emp_no`,`dept_no`), KEY `emp_no` (`emp_no`),"
        "  KEY `dept_no` (`dept_no`),"
        "  CONSTRAINT `dept_emp_ibfk_1` FOREIGN KEY (`emp_no`) "
        "     REFERENCES `employees` (`emp_no`) ON DELETE CASCADE,"
        "  CONSTRAINT `dept_emp_ibfk_2` FOREIGN KEY (`dept_no`) "
        "     REFERENCES `departments` (`dept_no`) ON DELETE CASCADE"
        ") ENGINE=InnoDB")

    TABLES['dept_manager'] = (
        "  CREATE TABLE `dept_manager` ("
        "  `emp_no` int(11) NOT NULL,"
        "  `dept_no` char(4) NOT NULL,"
        "  `from_date` date NOT NULL,"
        "  `to_date` date NOT NULL,"
        "  PRIMARY KEY (`emp_no`,`dept_no`),"
        "  KEY `emp_no` (`emp_no`),"
        "  KEY `dept_no` (`dept_no`),"
        "  CONSTRAINT `dept_manager_ibfk_1` FOREIGN KEY (`emp_no`) "
        "     REFERENCES `employees` (`emp_no`) ON DELETE CASCADE,"
        "  CONSTRAINT `dept_manager_ibfk_2` FOREIGN KEY (`dept_no`) "
        "     REFERENCES `departments` (`dept_no`) ON DELETE CASCADE"
        ") ENGINE=InnoDB")

    TABLES['titles'] = (
        "CREATE TABLE `titles` ("
        "  `emp_no` int(11) NOT NULL,"
        "  `title` varchar(50) NOT NULL,"
        "  `from_date` date NOT NULL,"
        "  `to_date` date DEFAULT NULL,"
        "  PRIMARY KEY (`emp_no`,`title`,`from_date`), KEY `emp_no` (`emp_no`),"
        "  CONSTRAINT `titles_ibfk_1` FOREIGN KEY (`emp_no`)"
        "     REFERENCES `employees` (`emp_no`) ON DELETE CASCADE"
        ") ENGINE=InnoDB")

    #  Use table
    try:
        cursor.execute("USE {}".format(DB_NAME))
    except mysql.connector.Error as err:
        print("Database {} does not exists.".format(DB_NAME))
        if err.errno == errorcode.ER_BAD_DB_ERROR:
            create_database()
            mysql_connection.database = DB_NAME
        else:
            print(err)
            exit(1)
    
    #  Create Tables

    for table_name in TABLES:
        table_description = TABLES[table_name]
        try:
            print("Creating table {}: ".format(table_name), end='')
            cursor.execute(table_description)
        except mysql.connector.Error as err:
            if err.errno == errorcode.ER_TABLE_EXISTS_ERROR:
                print("already exists.")
            else:
                print(err.msg)
        else:
            print("OK")

    cursor.close()
    mysql_connection.close()
        
create_tables()

Creating table employees: OK
Creating table departments: OK
Creating table salaries: OK
Creating table dept_emp: OK
Creating table dept_manager: OK
Creating table titles: OK

Function: Add data

In [95]:
from datetime import date, datetime, timedelta
import mysql.connector

def insert_data():
    ''' Add data'''
    
    cursor = mysql_connection.cursor()

    tomorrow = datetime.now().date() + timedelta(days=1)

    add_employee = ("INSERT INTO employees "
                   "(first_name, last_name, hire_date, gender, birth_date) "
                   "VALUES (%s, %s, %s, %s, %s)")
    add_salary = ("INSERT INTO salaries "
                  "(emp_no, salary, from_date, to_date) "
                  "VALUES (%(emp_no)s, %(salary)s, %(from_date)s, %(to_date)s)")

    data_employee = ('Geert', 'Vanderkelen', tomorrow, 'M', date(1977, 6, 14))

    # Insert new employee
    cursor.execute(add_employee, data_employee)
    emp_no = cursor.lastrowid

    # Insert salary information
    data_salary = {
      'emp_no': emp_no,
      'salary': 50000,
      'from_date': tomorrow,
      'to_date': date(9999, 1, 1),
    }
    cursor.execute(add_salary, data_salary)

    # Make sure data is committed to the database
    mysql_connection.commit()

    cursor.close()
    mysql_connection.close()

insert_data()

Fuction: Query

In [99]:
import datetime
import mysql.connector

def query_data():
    ''' Query'''

    cursor = mysql_connection.cursor()

    query = ("SELECT first_name, last_name, hire_date FROM employees "
             "WHERE hire_date BETWEEN %s AND %s")

    hire_start = datetime.date(1999, 1, 1)
    hire_end = datetime.date(2020, 12, 31)

    cursor.execute(query, (hire_start, hire_end))

    for (first_name, last_name, hire_date) in cursor:
      print("{}, {} was hired on {:%d %b %Y}".format(
        last_name, first_name, hire_date))

    cursor.close()
    mysql_connection.close()

query_data()

Vanderkelen, Geert was hired on 06 Oct 2020
In [ ]: