install python connector
python -m pip install mysql-connector
#verify the mysql connectivity
import mysql.connector
mydb = mysql.connector.connect(
host="localhost",
user="root",
passwd="p@ssw0rd"
)
print(mydb)
<mysql.connector.connection_cext.CMySQLConnection object at 0x000000E2CA88AB70>
#create a database
#display the databases
mycursor = mydb.cursor()
mycursor.execute("CREATE DATABASE School")
mycursor.execute("SHOW DATABASES")
for x in mycursor:
print(x)
('employee',)
('information_schema',)
('mysql',)
('performance_schema',)
('sakila',)
('school',)
('sys',)
('world',)
import socket
socket.getaddrinfo('127.0.0.1', 3306)
ALTER USER 'root'@'localhost' IDENTIFIED BY 'p@ssw0rd' PASSWORD EXPIRE NEVER;
ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'p@ssw0rd';
#connect with School database
#create a table
import mysql.connector
mydb = mysql.connector.connect(
host="localhost",
user="root",
passwd="p@ssw0rd",
database="school"
)
mycursor = mydb.cursor()
mycursor.execute("CREATE TABLE Student (rollnumber int, firstname varchar(50), lastname varchar(50), city varchar(50))")
mycursor.execute("SHOW TABLES")
for x in mycursor:
print(x)
('student',)
#Drop table
mycursor.execute("DROP TABLE Student")
for x in mycursor:
print(x)
#create a table with auto increment column
import mysql.connector
mydb = mysql.connector.connect(
host="localhost",
user="root",
passwd="p@ssw0rd",
database="school"
)
mycursor = mydb.cursor()
mycursor.execute("CREATE TABLE Student (rollnumber INT AUTO_INCREMENT PRIMARY KEY, firstname varchar(50), lastname varchar(50), city varchar(50))")
mycursor.execute("show tables")
for x in mycursor:
print(x)
('student',)
mydb = mysql.connector.connect(
host="localhost",
user="root",
passwd="p@ssw0rd",
database="school"
)
mycursor = mydb.cursor()
sql = "INSERT INTO student (firstname, lastname,city) VALUES (%s, %s, %s)"
val = ("Sankara","Narayanan","Pallathur")
mycursor.execute(sql, val)
mydb.commit()
print(mycursor.rowcount, "record inserted.")
1 record inserted.
mycursor = mydb.cursor()
sql = "INSERT INTO student (firstname, lastname,city) VALUES (%s, %s, %s)"
val = [
('Prathap','Pothan','திருவனந்தப்புரம்'),
('Rajini','Kanth','Bangalore'),
('Vijay', 'Balani','Bangalore'),
('Anbu','Sudha','Melasivapuri')
]
mycursor.executemany(sql, val)
mydb.commit()
print(mycursor.rowcount, "was inserted.")
4 was inserted.
Get Inserted identity ids:
sql = "INSERT INTO student (firstname, lastname,city) VALUES (%s, %s, %s)"
val = ("Arun","Vijay","Chennai")
mycursor.execute(sql, val)
mydb.commit()
print("1 record inserted, ID:", mycursor.lastrowid)
1 record inserted, ID: 6
select from table:
mycursor.execute("SELECT * FROM student")
myresult = mycursor.fetchall()
for x in myresult:
print(x)
(1, 'Sankara', 'Narayanan', 'Pallathur')
(2, 'Prathap', 'Pothan', 'திருவனந்தப்புரம்')
(3, 'Rajini', 'Kanth', 'Bangalore')
(4, 'Vijay', 'Balani', 'Bangalore')
(5, 'Anbu', 'Sudha', 'Melasivapuri')
(6, 'Arun', 'Vijay', 'Chennai')
#select columns
mycursor.execute("SELECT rollnumber as RollNumber, concat(firstname, ' ' ,lastname) as Name, city as City FROM student")
myresult = mycursor.fetchall()
for x in myresult:
print(x)
(1, 'Sankara Narayanan', 'Pallathur')
(2, 'Prathap Pothan', 'திருவனந்தப்புரம்')
(3, 'Rajini Kanth', 'Bangalore')
(4, 'Vijay Balani', 'Bangalore')
(5, 'Anbu Sudha', 'Melasivapuri')
(6, 'Arun Vijay', 'Chennai')
Fetch one record:
import mysql.connector
mydb = mysql.connector.connect(
host="localhost",
user="root",
passwd="p@ssw0rd",
database="school"
)
mycursor = mydb.cursor()
mycursor.execute("SELECT * FROM Student")
myresult = mycursor.fetchone()
print(myresult)
myresult = mycursor.fetchone()
print(myresult)
myresult = mycursor.fetchone()
print(myresult)
myresult = mycursor.fetchone()
print(myresult)
(1, 'Sankara', 'Narayanan', 'Pallathur')
(2, 'Prathap', 'Pothan', 'திருவனந்தப்புரம்')
(3, 'Rajini', 'Kanth', 'Bangalore')
(4, 'Vijay', 'Balani', 'Bangalore')
#where condition
import mysql.connector
mydb = mysql.connector.connect(
host="localhost",
user="root",
passwd="p@ssw0rd",
database="school"
)
mycursor = mydb.cursor()
sql = "SELECT * FROM student WHERE city ='Bangalore'"
mycursor.execute(sql)
myresult = mycursor.fetchall()
for x in myresult:
print(x)
(3, 'Rajini', 'Kanth', 'Bangalore')
(4, 'Vijay', 'Balani', 'Bangalore')
Like Condition:
import mysql.connector
mydb = mysql.connector.connect(
host="localhost",
user="root",
passwd="p@ssw0rd",
database="school"
)
mycursor = mydb.cursor()
sql = "SELECT * FROM student WHERE lastname LIKE '%an%'"
mycursor.execute(sql)
myresult = mycursor.fetchall()
for x in myresult:
print(x)
(1, 'Sankara', 'Narayanan', 'Pallathur')
(2, 'Prathap', 'Pothan', 'திருவனந்தப்புரம்')
(3, 'Rajini', 'Kanth', 'Bangalore')
(4, 'Vijay', 'Balani', 'Bangalore')
import mysql.connector
mydb = mysql.connector.connect(
host="localhost",
user="root",
passwd="p@ssw0rd",
database="school"
)
mycursor = mydb.cursor()
sql = "SELECT * FROM student WHERE lastname LIKE '%an'"
mycursor.execute(sql)
myresult = mycursor.fetchall()
for x in myresult:
print(x)
(1, 'Sankara', 'Narayanan', 'Pallathur')
(2, 'Prathap', 'Pothan', 'திருவனந்தப்புரம்')
Prevent SQL Injection:
import mysql.connector
mydb = mysql.connector.connect(
host="localhost",
user="root",
passwd="p@ssw0rd",
database="school"
)
mycursor = mydb.cursor()
sql = "SELECT * FROM student WHERE city = %s"
city = ("Bangalore", )
mycursor.execute(sql, city)
myresult = mycursor.fetchall()
for x in myresult:
print(x)
(3, 'Rajini', 'Kanth', 'Bangalore')
(4, 'Vijay', 'Balani', 'Bangalore')
Order by example (ascending : default):
import mysql.connector
mydb = mysql.connector.connect(
host="localhost",
user="root",
passwd="p@ssw0rd",
database="school"
)
mycursor = mydb.cursor()
sql = "SELECT * FROM student order by city"
mycursor.execute(sql)
myresult = mycursor.fetchall()
for x in myresult:
print(x)
(3, 'Rajini', 'Kanth', 'Bangalore')
(4, 'Vijay', 'Balani', 'Bangalore')
(6, 'Arun', 'Vijay', 'Chennai')
(5, 'Anbu', 'Sudha', 'Melasivapuri')
(1, 'Sankara', 'Narayanan', 'Pallathur')
(2, 'Prathap', 'Pothan', 'திருவனந்தப்புரம்')
Order by descending example:
import mysql.connector
mydb = mysql.connector.connect(
host="localhost",
user="root",
passwd="p@ssw0rd",
database="school"
)
mycursor = mydb.cursor()
sql = "SELECT * FROM student order by lastname desc"
mycursor.execute(sql)
myresult = mycursor.fetchall()
for x in myresult:
print(x)
(6, 'Arun', 'Vijay', 'Chennai')
(5, 'Anbu', 'Sudha', 'Melasivapuri')
(2, 'Prathap', 'Pothan', 'திருவனந்தப்புரம்')
(1, 'Sankara', 'Narayanan', 'Pallathur')
(3, 'Rajini', 'Kanth', 'Bangalore')
(4, 'Vijay', 'Balani', 'Bangalore')
Delete a record:
import mysql.connector
mydb = mysql.connector.connect(
host="localhost",
user="root",
passwd="p@ssw0rd",
database="school"
)
mycursor = mydb.cursor()
sql = "DELETE FROM student WHERE city = %s"
adr = ("திருவனந்தப்புரம்", )
mycursor.execute(sql, adr)
mydb.commit()
print(mycursor.rowcount, "record(s) deleted")
1 record(s) deleted
#drop a table
mycursor = mydb.cursor()
sql = "DROP TABLE student"
mycursor.execute(sql)
#Drop Only if Exist
mycursor = mydb.cursor()
sql = "DROP TABLE IF EXISTS student"
mycursor.execute(sql)
#update record with condition
#avoid sql injection
import mysql.connector
mydb = mysql.connector.connect(
host="localhost",
user="root",
passwd="p@ssw0rd",
database="school"
)
mycursor = mydb.cursor()
sql = "UPDATE Student SET lastname = %s WHERE lastname = %s"
val = ("Balaji", "Balani")
mycursor.execute(sql, val)
mydb.commit()
print(mycursor.rowcount, "record(s) affected")
1 record(s) affected
#limit example
import mysql.connector
mydb = mysql.connector.connect(
host="localhost",
user="root",
passwd="p@ssw0rd",
database="school"
)
mycursor = mydb.cursor()
mycursor.execute("SELECT * FROM Student LIMIT 5")
myresult = mycursor.fetchall()
for x in myresult:
print(x)
(1, 'Sankara', 'Narayanan', 'Pallathur')
(3, 'Rajini', 'Kanth', 'Bangalore')
(4, 'Vijay', 'Balaji', 'Bangalore')
(5, 'Anbu', 'Sudha', 'Melasivapuri')
(6, 'Arun', 'Vijay', 'Chennai')
#limit with offset example
import mysql.connector
mydb = mysql.connector.connect(
host="localhost",
user="root",
passwd="p@ssw0rd",
database="school"
)
mycursor = mydb.cursor()
mycursor.execute("SELECT * FROM Student LIMIT 3 offset 2")
myresult = mycursor.fetchall()
for x in myresult:
print(x)
(4, 'Vijay', 'Balaji', 'Bangalore')
(5, 'Anbu', 'Sudha', 'Melasivapuri')
(6, 'Arun', 'Vijay', 'Chennai')
