Python connect mysql with pymysql

PyMySQL is a pure-Python MySQL client library which allows you to easily connect to MySQL db and perform db operations in pythonic way.

Installation

$ pip install pymysql

Create db connection

import pymysql
import pymysql.cursors

# Connect to the database
connection = pymysql.connect(host='dbhost',
                             port='dbport',
                             user='dbuser',
                             password='dbpass',
                             db='dbname',
                             charset='utf8mb4',
                             cursorclass=pymysql.cursors.DictCursor)

Execute insert or update or delete sql

with connection.cursor() as cursor:
    # Create a new record
    sql = "INSERT INTO `users` (`email`, `password`) VALUES (%s, %s)"
    cursor.execute(sql, ('webmaster@python.org', 'you-never-know'))

# connection is not autocommit by default. So you must commit to save
# your changes.
connection.commit()

Execute select sql

with connection.cursor() as cursor:
    # Read a single record
    sql = "SELECT `id`, `password` FROM `users` WHERE `email`=%s"
    cursor.execute(sql, ('webmaster@python.org',))
    result = cursor.fetchone()
    print(result)

Close db connection

try:
  # db operation
  pass
finally:
  connection.close()

Reference

mysql database python