MySQL meets Python

Table of Contents

• Basic Installation
• Connecting to MySQL
• Creating Database
• Listing All Databases
• Create Table
• Check Tables in the given database
• Insert Data Into Table
• Inserting Multiple Rows
• Get Inserted ID
• Select From a Table
• Select Column
• Fetchone
• Filters
• Wildcard Characters
• Prevent SQL Injection
• Sorting
• Descending Order
• Delete Records
• Deleting by Preventing SQL Injection
• Update Table
• Limit the Result
• Drop

Basic Installation

sudo apt-get update
sudo apt-get install mysql-server

After installation

mysql -p

Enter the password set during installation

If above process does not work then we need to add user to mysql. This can be done by logging as ‘debian-sys-maint’. Following code gives us information (including password) of ‘debian-sys-maint’

sudo cat /etc/mysql/debian.cnf

login mysql as ‘debian-sys-maint’

mysql -u debian-sys-maint -p

Add new user to mysql

CREATE USER 'user'@'localhost' IDENTIFIED WITH mysql_native_password BY 'password';

Granting access to this user

GRANT CREATE, ALTER, DROP, INSERT, UPDATE, DELETE, SELECT, REFERENCES, RELOAD on *.* TO 'user'@'localhost' WITH GRANT OPTION;

Now exit mysql and run the command

mysql -p
#Enter the password set during the creation of user

Install python library for mysql

pip3 install mysql-connector-python

Connecting to MySQL

import mysql.connector
mydb = mysql.connector.connect(
    host="localhost",
    user="user",
    password="password"
)
print(mydb)

Output

<mysql.connector.connection_cext.CMySQLConnection object at 0x7fc2d6755550>

Creating Database

import mysql.connector

mydb = mysql.connector.connect(
    host="localhost",
    user="user",
    password="password"
)
mycursor = mydb.cursor()
mycursor.execute("CREATE DATABASE mydatabase;")

Listing All Databases

import mysql.connector

mydb = mysql.connector.connect(
  host="localhost",
  user="user",
  password="password"
)

mycursor = mydb.cursor()

mycursor.execute("SHOW DATABASES")

for x in mycursor:
  print(x)

Output

(‘information_schema’,)
(‘database2’,)
(‘m’,)
(‘mydatabase’,)
(‘mysql’,)
(‘performance_schema’,)
(‘sys’,)

Create Table

import mysql.connector

mydb = mysql.connector.connect(
  host="localhost",
  user="user",
  password="password",
  database="mydatabase"
)

mycursor = mydb.cursor()
mycursor.execute("CREATE TABLE customers (id INT AUTO_INCREMENT PRIMARY KEY, name VARCHAR(255),address VARCHAR(255))")

Check Tables in the given database

import mysql.connector

mydb = mysql.connector.connect(
  host="localhost",
  user="user",
  password="password",
  database="mydatabase"
)

mycursor = mydb.cursor()

mycursor.execute("SHOW TABLES")

for x in mycursor:
  print(x)

Output

(‘customers’,)

Insert Data Into Table

import mysql.connector

mydb = mysql.connector.connect(
  host="localhost",
  user="user",
  password="password",
  database="mydatabase"
)

mycursor = mydb.cursor()

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

mydb.commit()

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

Output

1 record inserted.

Inserting Multiple Rows

To insert multiple rows into a table, use the executemany() method.

The second parameter of the executemany() method is a list of tuples, containing the data you want to insert:

import mysql.connector

mydb = mysql.connector.connect(
  host="localhost",
  user="user",
  password="password",
  database="mydatabase"
)

mycursor = mydb.cursor()

sql = "INSERT INTO customers (name, address) VALUES (%s, %s)"
val = [
  ('Hari', 'Anamnagar'),
  ('Murakami', 'Apple pie'),
  ('Hannah', 'Mountain child'),
  ('Xin', 'Hevenlyhell'),
  ('Yang', 'Egg park'),
  ('Kafka', 'wonderland'),
]

mycursor.executemany(sql, val)

mydb.commit()

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

Output

6 was inserted.

Get Inserted ID

You can get the id of the row you just inserted by asking the cursor object.

import mysql.connector

mydb = mysql.connector.connect(
  host="localhost",
  user="user",
  password="password",
  database="mydatabase"
)

mycursor = mydb.cursor()

sql = "INSERT INTO customers (name, address) VALUES (%s, %s)"
val = ("Mirza", "Lalaland")
mycursor.execute(sql, val)

mydb.commit()

print("1 record inserted, ID:", mycursor.lastrowid)

Output

1 record inserted, ID: 8

Select From a Table

import mysql.connector

mydb = mysql.connector.connect(
  host="localhost",
  user="user",
  password="password",
  database="mydatabase"
)

mycursor = mydb.cursor()

mycursor.execute("SELECT * FROM customers")

myresult = mycursor.fetchall()

for x in myresult:
  print(x)

Output

(1, ‘John’, ‘Highway 21’)
(2, ‘Hari’, ‘Anamnagar’)
(3, ‘Murakami’, ‘Apple pie’)
(4, ‘Hannah’, ‘Mountain child’)
(5, ‘Xin’, ‘Hevenlyhell’)
(6, ‘Yang’, ‘Egg park’)
(7, ‘Kafka’, ‘wonderland’)
(8, ‘Mirza’, ‘Lalaland’)

Select Column

import mysql.connector

mydb = mysql.connector.connect(
  host="localhost",
  user="user",
  password="password",
  database="mydatabase"
)

mycursor = mydb.cursor()

mycursor.execute("SELECT name FROM customers")

myresult = mycursor.fetchall()

for x in myresult:
  print(x)

Output

(‘John’,)
(‘Hari’,)
(‘Murakami’,)
(‘Hannah’,)
(‘Xin’,)
(‘Yang’,)
(‘Kafka’,)
(‘Mirza’,)

Fetchone

import mysql.connector

mydb = mysql.connector.connect(
  host="localhost",
  user="user",
  password="password",
  database="mydatabase"
)

mycursor = mydb.cursor()

mycursor.execute("SELECT * FROM customers")

myresult = mycursor.fetchone()

print(myresult)

Output

(1, ‘John’, ‘Highway 21’)

Filters

import mysql.connector

mydb = mysql.connector.connect(
  host="localhost",
  user="user",
  password="password",
  database="mydatabase"
)

mycursor = mydb.cursor()

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

mycursor.execute(sql)

myresult = mycursor.fetchall()

for x in myresult:
  print(x)

Output

(8, ‘Mirza’, ‘Lalaland’)

Wildcard Characters

You can also select the records that starts, includes, or ends with a given letter or phrase.

Use the %  to represent wildcard characters:

import mysql.connector

mydb = mysql.connector.connect(
  host="localhost",
  user="user",
  password="password",
  database="mydatabase"
)

mycursor = mydb.cursor()

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

mycursor.execute(sql)

myresult = mycursor.fetchall()

for x in myresult:
  print(x)

Output

(7, ‘Kafka’, ‘wonderland’)
(8, ‘Mirza’, ‘Lalaland’)

Prevent SQL Injection

When query values are provided by the user, you should escape the values.

This is to prevent SQL injections, which is a common web hacking technique to destroy or misuse your database.

import mysql.connector

mydb = mysql.connector.connect(
  host="localhost",
  user="user",
  password="password",
  database="mydatabase"
)

mycursor = mydb.cursor()

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

mycursor.execute(sql, adr)

myresult = mycursor.fetchall()

for x in myresult:
  print(x)

Sorting

import mysql.connector

mydb = mysql.connector.connect(
  host="localhost",
  user="user",
  password="password",
  database="mydatabase"
)

mycursor = mydb.cursor()

sql = "SELECT * FROM customers ORDER BY name"

mycursor.execute(sql)

myresult = mycursor.fetchall()

for x in myresult:
  print(x)

Output

(4, ‘Hannah’, ‘Mountain child’)
(2, ‘Hari’, ‘Anamnagar’)
(1, ‘John’, ‘Highway 21’)
(7, ‘Kafka’, ‘wonderland’)
(8, ‘Mirza’, ‘Lalaland’)
(3, ‘Murakami’, ‘Apple pie’)
(5, ‘Xin’, ‘Hevenlyhell’)
(6, ‘Yang’, ‘Egg park’)

Descending Order

import mysql.connector

mydb = mysql.connector.connect(
  host="localhost",
  user="user",
  password="password",
  database="mydatabase"
)

mycursor = mydb.cursor()

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

mycursor.execute(sql)

myresult = mycursor.fetchall()

for x in myresult:
  print(x)

Output

(6, ‘Yang’, ‘Egg park’)
(5, ‘Xin’, ‘Hevenlyhell’)
(3, ‘Murakami’, ‘Apple pie’)
(8, ‘Mirza’, ‘Lalaland’)
(7, ‘Kafka’, ‘wonderland’)
(1, ‘John’, ‘Highway 21’)
(2, ‘Hari’, ‘Anamnagar’)
(4, ‘Hannah’, ‘Mountain child’)

Delete Records

import mysql.connector

mydb = mysql.connector.connect(
  host="localhost",
  user="user",
  password="password",
  database="mydatabase"
)

mycursor = mydb.cursor()

sql = "DELETE FROM customers WHERE name = 'Mirza'"

mycursor.execute(sql)

mydb.commit()

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

Output

1 record(s) deleted

Deleting by Preventing SQL Injection

import mysql.connector

mydb = mysql.connector.connect(
  host="localhost",
  user="user",
  password="password",
  database="mydatabase"
)

mycursor = mydb.cursor()

sql = "DELETE FROM customers WHERE address = %s"
adr = ("Egg park", )

mycursor.execute(sql, adr)

mydb.commit()

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

Output

1 record(s) deleted

Update Table

The table before updating

(1, ‘John’, ‘Highway 21’)
(2, ‘Hari’, ‘Anamnagar’)
(3, ‘Murakami’, ‘Apple pie’)
(4, ‘Hannah’, ‘Mountain child’)
(5, ‘Xin’, ‘Hevenlyhell’)
(7, ‘Shantanu’, ‘wonderland’)

import mysql.connector

mydb = mysql.connector.connect(
  host="localhost",
  user="user",
  password="password",
  database="mydatabase"
)

mycursor = mydb.cursor()

sql = "UPDATE customers SET name = 'Shantanu' WHERE name = 'Kafka'"

mycursor.execute(sql)

mydb.commit()

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

The table after update

(1, ‘John’, ‘Highway 21’)
(2, ‘Hari’, ‘Anamnagar’)
(3, ‘Murakami’, ‘Apple pie’)
(4, ‘Hannah’, ‘Mountain child’)
(5, ‘Xin’, ‘Hevenlyhell’)
(7, ‘Shantanu’, ‘wonderland’)

Updating while Preventing SQL Injection

import mysql.connector

mydb = mysql.connector.connect(
  host="localhost",
  user="user",
  password="password",
  database="mydatabase"
)

mycursor = mydb.cursor()

sql = "UPDATE customers SET name = %s WHERE name = %s"
val = ("Joko Widodo","Shantanu")
mycursor.execute(sql, val)

mydb.commit()

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

Table after above code

(1, ‘John’, ‘Highway 21’)
(2, ‘Hari’, ‘Anamnagar’)
(3, ‘Murakami’, ‘Apple pie’)
(4, ‘Hannah’, ‘Mountain child’)
(5, ‘Xin’, ‘Hevenlyhell’)
(7, ‘Joko Widodo’, ‘wonderland’)

Limit the Result

import mysql.connector

mydb = mysql.connector.connect(
  host="localhost",
  user="user",
  password="password",
  database="mydatabase"
)

mycursor = mydb.cursor()

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

myresult = mycursor.fetchall()

for x in myresult:
  print(x)

Output

(1, ‘John’, ‘Highway 21’)
(2, ‘Hari’, ‘Anamnagar’)
(3, ‘Murakami’, ‘Apple pie’)
(4, ‘Hannah’, ‘Mountain child’)
(5, ‘Xin’, ‘Hevenlyhell’)

Start from position 3, and return 2 records:

import mysql.connector

mydb = mysql.connector.connect(
  host="localhost",
  user="user",
  password="password",
  database="mydatabase"
)

mycursor = mydb.cursor()

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

myresult = mycursor.fetchall()

for x in myresult:
  print(x)

Output

(3, ‘Murakami’, ‘Apple pie’)
(4, ‘Hannah’, ‘Mountain child’)

DROP TABLE

import mysql.connector

mydb = mysql.connector.connect(
  host="localhost",
  user="user",
  password="password",
  database="mydatabase"
)

mycursor = mydb.cursor()

sql = "DROP TABLE IF EXISTS customers"

mycursor.execute(sql)

Drop Database

import mysql.connector

mydb = mysql.connector.connect(
  host="localhost",
  user="user",
  password="password",
  database="mydatabase"
)

mycursor = mydb.cursor()

sql = "DROP DATABASE IF EXISTS mydatabase"

mycursor.execute(sql)