Saturday , September 18 2021
Home / Uncategorized / Python MySQL Database

Python MySQL Database

Python MySQL Database

Python MySQL Database

Python can be used in database applications. One of the most popular databases is MySQL. Python needs a MySQL driver to access the MySQL database.

  • pip install mysql-connector-python

MySQL Database

To be able to experiment with the code examples in this tutorial, you should have MySQL installed on your computer.

You can download XAMPP a free MySQL database at https://www.apachefriends.org/download.html. After download the XAMPP Start Apache and MySQL Server.

Test MySQL Connector

In [1]:
import mysql.connector

Note: If the above code was executed with no errors, “MySQL Connector” is installed and ready to be used.

Python MySQL Create Database

To create a database in MySQL, use the “CREATE DATABASE” statement:

In [2]:
import mysql.connector
mydb = mysql.connector.connect(
  host="localhost",
  user="root",
  passwd=""
)
mycursor = mydb.cursor()
mycursor.execute("create database itronix")

If the above code was executed with no errors, you have successfully created a database

Check if Database Exists

You can check if a database exist by listing all databases in your system by using the “SHOW DATABASES” statement:

In [3]:
import mysql.connector
mydb = mysql.connector.connect(
  host="localhost",
  user="root",
  passwd=""
)
mycursor = mydb.cursor()
mycursor.execute("show databases")

for x in mycursor:
    print(x)
('information_schema',)
('itronix',)
('mysql',)
('performance_schema',)
('phpmyadmin',)
('ritesh',)

You can also verify in browser by opening phymyadmin: http://localhost/phpmyadmin/

Python MySQL Create Table

To create a table in MySQL, use the “CREATE TABLE” statement. Make sure you define the name of the database when you create the connection

In [4]:
import mysql.connector
mydb = mysql.connector.connect(
  host="localhost",
  user="root",
  passwd="",
  database="itronix"
)
mycursor = mydb.cursor()
mycursor.execute("CREATE TABLE student (name VARCHAR(255), address VARCHAR(255), Phone int)")

Check if Table Exists

You can check if a table exist by listing all tables in your database with the “SHOW TABLES” statement:

In [5]:
import mysql.connector
mydb = mysql.connector.connect(
  host="localhost",
  user="root",
  passwd="",
  database="itronix"
)
mycursor = mydb.cursor()
mycursor.execute("show tables")
for x in mycursor:
    print(x)
('student',)

Drop Table

In [6]:
import mysql.connector
mydb = mysql.connector.connect(
  host="localhost",
  user="root",
  passwd="",
  database="itronix"
)
mycursor = mydb.cursor()
mycursor.execute("drop table student")

Creating Table with Primary Key

  • When creating a table, you should also create a column with a unique key for each record.
  • This can be done by defining a PRIMARY KEY.
  • We use the statement “INT AUTO_INCREMENT PRIMARY KEY” which will insert a unique number for each record. Starting at 1, and increased by one for each record.
In [7]:
import mysql.connector
mydb = mysql.connector.connect(
  host="localhost",
  user="root",
  passwd="",
  database="itronix"
)
mycursor = mydb.cursor()
mycursor.execute("CREATE TABLE customers (id INT AUTO_INCREMENT PRIMARY KEY, name VARCHAR(255), address VARCHAR(255))")

If the table already exists, use the ALTER TABLE keyword:

  • mycursor.execute(“ALTER TABLE customers ADD COLUMN id INT AUTO_INCREMENT PRIMARY KEY”)

Python MySQL Insert Into Table

To fill a table in MySQL, use the “INSERT INTO” statement.

In [8]:
import mysql.connector
mydb = mysql.connector.connect(
  host="localhost",
  user="root",
  passwd="",
  database="itronix"
)

mycursor = mydb.cursor()

name = input("Enter Name:")
address = input("Enter Address:")

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

mydb.commit()
print(mycursor.rowcount, "record inserted.")
Enter Name:Karan
Enter Address:Ludhiana
1 record inserted.

Note: The statement: mydb.commit(). It is required to make the changes, otherwise no changes are made to the table.

Insert 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:
In [9]:
import mysql.connector
mydb = mysql.connector.connect(
  host="localhost",
  user="root",
  passwd="",
  database="itronix"
)

mycursor = mydb.cursor()

sql = "INSERT INTO customers (name, address) VALUES (%s, %s)"
val = [
  ('Kevin', 'Dublin 4'),
  ('Keisha', 'Otawa 4'),
  ('Varun', 'Ontario'),
  ('Priya', 'Delhi'),
  ('Atharv', 'Goa'),
]

mycursor.executemany(sql, val)

mydb.commit()
print(mycursor.rowcount, "record inserted.")
5 record inserted.

Get the last Inserted ID

  • You can get the id of the row you just inserted by asking the cursor object.
In [10]:
import mysql.connector
mydb = mysql.connector.connect(
  host="localhost",
  user="root",
  passwd="",
  database="itronix"
)

mycursor = mydb.cursor()

name = input("Enter Name:")
address = input("Enter Address:")

sql = "INSERT INTO customers (name, address) VALUES (%s, %s)"
val = (name,address)

mycursor.execute(sql, val)

mydb.commit()
print("1 record inserted, ID:", mycursor.lastrowid)
Enter Name:Sia
Enter Address:Dubai
1 record inserted, ID: 7

Python MySQL Select From Table

To select from a table in MySQL, use the “SELECT” statement:

In [11]:
import mysql.connector
mydb = mysql.connector.connect(
  host="localhost",
  user="root",
  passwd="",
  database="itronix"
)

mycursor = mydb.cursor()

mycursor.execute("SELECT * FROM customers")

myresult = mycursor.fetchall()

for x in myresult:
    print(x)
(1, 'Karan', 'Ludhiana')
(2, 'Kevin', 'Dublin 4')
(3, 'Keisha', 'Otawa 4')
(4, 'Varun', 'Ontario')
(5, 'Priya', 'Delhi')
(6, 'Atharv', 'Goa')
(7, 'Sia', 'Dubai')

Selecting Columns

  • To select only some of the columns in a table, use the “SELECT” statement followed by the column name(s):
In [12]:
import mysql.connector
mydb = mysql.connector.connect(
  host="localhost",
  user="root",
  passwd="",
  database="itronix"
)

mycursor = mydb.cursor()

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

myresult = mycursor.fetchall()

for x in myresult:
    print(x)
('Karan', 'Ludhiana')
('Kevin', 'Dublin 4')
('Keisha', 'Otawa 4')
('Varun', 'Ontario')
('Priya', 'Delhi')
('Atharv', 'Goa')
('Sia', 'Dubai')

Using the fetchone() Method

  • If you are only interested in one row, you can use the fetchone() method.
  • The fetchone() method will return the first row of the result:
In [13]:
import mysql.connector
mydb = mysql.connector.connect(
  host="localhost",
  user="root",
  passwd="",
  database="itronix"
)

mycursor = mydb.cursor()

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

myresult = mycursor.fetchone()

print(myresult)
('Karan', 'Ludhiana')

Python MySQL Where

In [14]:
import mysql.connector
mydb = mysql.connector.connect(
  host="localhost",
  user="root",
  passwd="",
  database="itronix"
)

mycursor = mydb.cursor()
sql = "SELECT * FROM customers WHERE address ='Ludhiana'"
mycursor.execute(sql)

myresult = mycursor.fetchall()
for x in myresult:
    print(x)
(1, 'Karan', 'Ludhiana')

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:
In [15]:
import mysql.connector
mydb = mysql.connector.connect(
  host="localhost",
  user="root",
  passwd="",
  database="itronix"
)

mycursor = mydb.cursor()
sql = "SELECT * FROM customers WHERE address LIKE '%lud%'"
mycursor.execute(sql)

myresult = mycursor.fetchall()
for x in myresult:
    print(x)
(1, 'Karan', 'Ludhiana')

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.
  • The mysql.connector module has methods to escape query values:
In [16]:
import mysql.connector
mydb = mysql.connector.connect(
  host="localhost",
  user="root",
  passwd="",
  database="itronix"
)

mycursor = mydb.cursor()
sql = "SELECT * FROM customers WHERE address = %s"
adr = ("Otawa 4", )

mycursor.execute(sql, adr)
myresult = mycursor.fetchall()

for x in myresult:
    print(x)
(3, 'Keisha', 'Otawa 4')

Python MySQL Order By

Sort the Result

  • Use the ORDER BY statement to sort the result in ascending or descending order.
  • The ORDER BY keyword sorts the result ascending by default. To sort the result in descending order, use the DESC keyword.
In [17]:
import mysql.connector
mydb = mysql.connector.connect(
  host="localhost",
  user="root",
  passwd="",
  database="itronix"
)

mycursor = mydb.cursor()
sql = "SELECT * FROM customers ORDER BY name"

mycursor.execute(sql)
myresult = mycursor.fetchall()

for x in myresult:
    print(x)
(6, 'Atharv', 'Goa')
(1, 'Karan', 'Ludhiana')
(3, 'Keisha', 'Otawa 4')
(2, 'Kevin', 'Dublin 4')
(5, 'Priya', 'Delhi')
(7, 'Sia', 'Dubai')
(4, 'Varun', 'Ontario')

ORDER BY DESC

In [18]:
import mysql.connector
mydb = mysql.connector.connect(
  host="localhost",
  user="root",
  passwd="",
  database="itronix"
)

mycursor = mydb.cursor()
sql = "SELECT * FROM customers ORDER BY name DESC"

mycursor.execute(sql)
myresult = mycursor.fetchall()

for x in myresult:
    print(x)
(4, 'Varun', 'Ontario')
(7, 'Sia', 'Dubai')
(5, 'Priya', 'Delhi')
(2, 'Kevin', 'Dublin 4')
(3, 'Keisha', 'Otawa 4')
(1, 'Karan', 'Ludhiana')
(6, 'Atharv', 'Goa')

Python MySQL Delete From By

  • You can delete records from an existing table by using the “DELETE FROM” statement:
In [19]:
import mysql.connector
mydb = mysql.connector.connect(
  host="localhost",
  user="root",
  passwd="",
  database="itronix"
)

mycursor = mydb.cursor()
sql = "DELETE FROM customers WHERE address = 'Otawa 4'"
mycursor.execute(sql)
mydb.commit()

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

Prevent SQL Injection

  • It is considered a good practice to escape the values of any query, also in delete statements.
  • This is to prevent SQL injections, which is a common web hacking technique to destroy or misuse your database.
  • The mysql.connector module uses the placeholder %s to escape values in the delete statement:
In [20]:
import mysql.connector
mydb = mysql.connector.connect(
  host="localhost",
  user="root",
  passwd="",
  database="itronix"
)

mycursor = mydb.cursor()

sql = "DELETE FROM customers WHERE address = %s"
adr = ("dublin 4", )

mycursor.execute(sql, adr)
mydb.commit()

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

Python MySQL Update Table

You can update existing records in a table by using the “UPDATE” statement:

In [22]:
import mysql.connector
mydb = mysql.connector.connect(
  host="localhost",
  user="root",
  passwd="",
  database="itronix"
)

mycursor = mydb.cursor()
sql = "UPDATE customers SET address = 'Detroit' WHERE address = 'Ludhiana'"
mycursor.execute(sql)
mydb.commit()

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

Python MySQL Limit

  • You can limit the number of records returned from the query, by using the “LIMIT” statement:
In [23]:
import mysql.connector
mydb = mysql.connector.connect(
  host="localhost",
  user="root",
  passwd="",
  database="itronix"
)
mycursor = mydb.cursor()
mycursor.execute("SELECT * FROM customers LIMIT 5")
myresult = mycursor.fetchall()

for x in myresult:
    print(x)
(1, 'Karan', 'Detroit')
(4, 'Varun', 'Ontario')
(5, 'Priya', 'Delhi')
(6, 'Atharv', 'Goa')
(7, 'Sia', 'Dubai')

Start From Another Position

  • If you want to return five records, starting from the third record, you can use the “OFFSET” keyword:
In [24]:
import mysql.connector
mydb = mysql.connector.connect(
  host="localhost",
  user="root",
  passwd="",
  database="itronix"
)
mycursor = mydb.cursor()
mycursor.execute("SELECT * FROM customers LIMIT 5 OFFSET 2")
myresult = mycursor.fetchall()

for x in myresult:
    print(x)
(5, 'Priya', 'Delhi')
(6, 'Atharv', 'Goa')
(7, 'Sia', 'Dubai')

Python MySQL Join

  • Join Two or More Tables
  • You can combine rows from two or more tables, based on a related column between them, by using a JOIN statement.
  • Consider you have a “customers” table and a “product” table:

Create Table Name Product

In [29]:
import mysql.connector
mydb = mysql.connector.connect(
  host="localhost",
  user="root",
  passwd="",
  database="itronix"
)

mycursor = mydb.cursor()
mycursor.execute("CREATE TABLE product (id int, prodname VARCHAR(255))")

sql = "INSERT INTO product (id, prodname) VALUES (%s, %s)"
val = [
  ('1', 'iPhone'),
  ('2', 'Charger'),
  ('3', 'Data Cable'),
  ('4', 'Laptop'),
  ('5', 'Pen Drive'),
]

mycursor.executemany(sql, val)

mydb.commit()
print(mycursor.rowcount, "record inserted.")
5 record inserted.

Inner Join is same as Join

In [45]:
import mysql.connector
mydb = mysql.connector.connect(
  host="localhost",
  user="root",
  passwd="",
  database="itronix"
)

mycursor = mydb.cursor()

sql = "SELECT * FROM customers JOIN product ON customers.id = product.id"

mycursor.execute(sql)

myresult = mycursor.fetchall()

for x in myresult:
    print(x)
(1, 'Karan', 'Detroit', 1, 'iPhone')
(4, 'Varun', 'Ontario', 4, 'Laptop')
(5, 'Priya', 'Delhi', 5, 'Pen Drive')

Select Columns as per require

In [36]:
import mysql.connector
mydb = mysql.connector.connect(
  host="localhost",
  user="root",
  passwd="",
  database="itronix"
)

mycursor = mydb.cursor()

sql = "SELECT customers.id,customers.name,customers.address,product.prodname \
       FROM customers JOIN product ON customers.id = product.id"

mycursor.execute(sql)

myresult = mycursor.fetchall()

for x in myresult:
    print(x)
(1, 'Karan', 'Detroit', 'iPhone')
(4, 'Varun', 'Ontario', 'Laptop')
(5, 'Priya', 'Delhi', 'Pen Drive')

Python MySQL Inner Join

In [37]:
import mysql.connector
mydb = mysql.connector.connect(
  host="localhost",
  user="root",
  passwd="",
  database="itronix"
)

mycursor = mydb.cursor()

sql = "SELECT customers.id,customers.name,customers.address,product.prodname \
       FROM customers INNER JOIN product ON customers.id = product.id"

mycursor.execute(sql)

myresult = mycursor.fetchall()

for x in myresult:
    print(x)
(1, 'Karan', 'Detroit', 'iPhone')
(4, 'Varun', 'Ontario', 'Laptop')
(5, 'Priya', 'Delhi', 'Pen Drive')

Python MySQL Left Join

In [38]:
import mysql.connector
mydb = mysql.connector.connect(
  host="localhost",
  user="root",
  passwd="",
  database="itronix"
)

mycursor = mydb.cursor()

sql = "SELECT customers.id,customers.name,customers.address,product.prodname \
       FROM customers LEFT JOIN product ON customers.id = product.id"

mycursor.execute(sql)

myresult = mycursor.fetchall()

for x in myresult:
    print(x)
(1, 'Karan', 'Detroit', 'iPhone')
(4, 'Varun', 'Ontario', 'Laptop')
(5, 'Priya', 'Delhi', 'Pen Drive')
(6, 'Atharv', 'Goa', None)
(7, 'Sia', 'Dubai', None)

Python MySQL Right Join

In [39]:
import mysql.connector
mydb = mysql.connector.connect(
  host="localhost",
  user="root",
  passwd="",
  database="itronix"
)

mycursor = mydb.cursor()

sql = "SELECT customers.id,customers.name,customers.address,product.prodname \
       FROM customers RIGHT JOIN product ON customers.id = product.id"

mycursor.execute(sql)

myresult = mycursor.fetchall()

for x in myresult:
    print(x)
(1, 'Karan', 'Detroit', 'iPhone')
(None, None, None, 'Charger')
(None, None, None, 'Data Cable')
(4, 'Varun', 'Ontario', 'Laptop')
(5, 'Priya', 'Delhi', 'Pen Drive')

Python MySQL OUTER or FULL Join

  • You don’t have FULL JOINS on MySQL, but you can sure emulate them using Union
In [47]:
import mysql.connector
mydb = mysql.connector.connect(
  host="localhost",
  user="root",
  passwd="",
  database="itronix"
)

mycursor = mydb.cursor()

sql = "SELECT * from customers as t1 LEFT JOIN product as t2 ON t1.id = t2.id \
 UNION SELECT * from customers as t1 RIGHT JOIN product as t2 ON t1.id = t2.id "

mycursor.execute(sql)

myresult = mycursor.fetchall()

for x in myresult:
    print(x)
(1, 'Karan', 'Detroit', 1, 'iPhone')
(4, 'Varun', 'Ontario', 4, 'Laptop')
(5, 'Priya', 'Delhi', 5, 'Pen Drive')
(6, 'Atharv', 'Goa', None, None)
(7, 'Sia', 'Dubai', None, None)
(None, None, None, 2, 'Charger')
(None, None, None, 3, 'Data Cable')

About Machine Learning

Check Also

Logistic Regression

Leave a Reply

Your email address will not be published. Required fields are marked *