How to Connect to Mysql Database in Python

In this example we will show how to connect mysql database and operate queries in it with Python.

Source Code

#! /usr/bin/env python3
# -*- coding: utf-8 -*-

import pymysql

db = pymysql.connect("localhost","root","1234","test")
cursor = db.cursor()
cursor.execute("DROP TABLE IF EXISTS EMPLOYEE")
# Create new table query
sql1 = """CREATE TABLE EMPLOYEE (
         FIRST_NAME  CHAR(20) NOT NULL,
         LAST_NAME  CHAR(20),
         AGE INT,  
         SEX CHAR(1),
         INCOME FLOAT )"""

# Insert record query
sql2= """INSERT INTO EMPLOYEE (FIRST_NAME,
         LAST_NAME, AGE, SEX, INCOME)
         VALUES ('James', 'Smith', 20, 'M', 60000)"""
try:
   cursor.execute(sql1)
   cursor.execute(sql2)
   db.commit()
except:
   db.rollback()

db.close()