In order to connect python with MySql, I need to install the MySQL driver package.
cmd>python -m pip install mysql-connector-python
Remember if you want to install MySQL connector in the virtual environment, enable or activate the environment and then install the driver using PIP.
MySql connection
import mysql.connector mydb = mysql.connector.connect( host="localhost", user="root", password="" ) mycursor = mydb.cursor() print(mydb)
This will print nothing if the connection is successful else return an error.
Create database
import mysql.connector mydb = mysql.connector.connect( host="localhost", user="root", password="" ) mycursor = mydb.cursor() mycursor.execute("Create Database school_database") print(mydb)
This will create the database name school_database.
Show all databases of MySql
import mysql.connector mydb = mysql.connector.connect( host="localhost", user="root", password="" ) mycursor = mydb.cursor() mycursor.execute("show databases") for x in mycursor: print(x) print(mydb)
This will print all databases of MySql.
Create Tables
import mysql.connector mydb = mysql.connector.connect( host="localhost", user="root", password="", database="school_database" ) mycursor = mydb.cursor() #create table student mycursor.execute("CREATE TABLE students (name VARCHAR(255), address VARCHAR(255))") #create table teachers with primary key id mycursor.execute("CREATE TABLE teachers(id INT AUTO_INCREMENT PRIMARY KEY, name VARCHAR(255), address VARCHAR(255))") #show all tables mycursor.execute("SHOW TABLES") for x in mycursor: print(x)
Here two tables will create students and teachers and print all tables of the database school_database.
Insert record
import mysql.connector mydb = mysql.connector.connect( host="localhost", user="root", password="", database="school_database" ) mycursor = mydb.cursor() #method 1 sql = "insert into teachers(name,address) values('amit','noida')" mysql.execute(sql) #method 2 protect from sql injection sql = "insert into teachers(name, address) values(%s,%s)" val = ("Sunil","Delhi") mycursor.execute(sql,val) #method 3 insert many sql = "insert into teachers(name, address) values(%s, %s)" val = [ ("sunil","111"), ("amit","222"), ("sumit","333") ] mycursor.executemany(sql,val) # must run to save mydb.commit() print(mycursor.rowcount, " record inserted"); #print last insert id print(mycursor.lastrowid, " row id");
Update record
import mysql.connector mydb = mysql.connector.connect( host="localhost", user="root", password="", database = "school_database" ) mycursor = mydb.cursor() # single line sql = "update teachers set name=%s where address = %s" val = ('Sunita','Delhi') mycursor.execute(sql,val) # multi line # \ at the end mean statement continue to next line sql = "update teachers \ set name=%s \ where address = %s" val = ('Sunita','Delhi') mycursor.execute(sql,val) mydb.commit() print(mycursor.rowcount, " rows updated")
Delete Record
import mysql.connector mydb = mysql.connector.connect( host="localhost", user="root", password="", database ="school_database" ) mycursor = mydb.cursor() sql = "delete from customers where name like %s" address = ('A%',) mycursor.execute(sql,address) mydb.commit() print(mycursor.rowcount, " Record deleted")
FetchAll Records
import mysql.connector mydb = mysql.connector.connect( host="localhost", user="root", password="", database ="school_database" ) mycursor = mydb.cursor() mycursor.execute("select name,address from teachers") myresult = mycursor.fetchall() for x in myresult: print(x)
Fetch one record
import mysql.connector mydb = mysql.connector.connect( host="localhost", user="root", password="", database ="school_database" ) mycursor = mydb.cursor() sql = "select name,address from teachers where address = %s") val = ('A%',) mycursor.execute(sql,val) myresult = mycursor.fetchone() print(myresult)