Python Database API ( Application Program Interface ) is the Database interface for the standard Python. This standard is adhered to by most Python Database interfaces. There are various Database servers supported by Python Databases such as MySQL, GadFly, PostgreSQL, Microsoft SQL Server 2000, Informix, Interbase, Oracle, Sybase, etc. To connect with MySQL database server from Python, we need to import the mysql.connector interface. Below is a program to connect with MySQL database geeks.
Python# importing required libraryimportmysql.connector# connecting to the databasedataBase=mysql.connector.connect(host="localhost",user="user",passwd="pswrd",database="geeks")# preparing a cursor objectcursorObject=dataBase.cursor()# disconnecting from serverdataBase.close()
The above program illustrates the connection with the MySQL database geeks in which host-name is localhost, the username is user and password is pswrd.
Deleting query from tables
After connecting with the database in MySQL we can create tables in it and can manipulate them.Syntax Statement:
DELETE FROM TABLE_NAME WHERE ATTRIBUTE_NAME = ATTRIBUTE_VALUE
Example 1: Below is a program to delete a query from the table in the database.
Python# importing required libraryimportmysql.connector# connecting to the databasedataBase=mysql.connector.connect(host="localhost",user="user",passwd="pswrd",database="geeks")# preparing a cursor objectcursorObject=dataBase.cursor()# creating tablestudentRecord="""CREATETABLESTUDENT(NAMEVARCHAR(20)NOTNULL,BRANCHVARCHAR(50),ROLLINTNOTNULL,SECTIONVARCHAR(5),AGEINT)"""# table createdcursorObject.execute(studentRecord)# inserting data into the tablequery="INSERTINTOSTUDENT(NAME,BRANCH,ROLL,SECTION,AGE)VALUES(%s,%s)"attrValues=("RiturajSaha","InformationTechnology","1706256","IT-3","20")cursorObject.execute(query,attrValues)attrValues=("RitamBarik","InformationTechnology","1706254","IT-3","21")cursorObject.execute(query,attrValues)attrValues=("RishiKumar","InformationTechnology","1706253","IT-3","21")cursorObject.execute(query,attrValues)# deleting queryquery="DELETEFROMSTUDENTWHEREROLL=1706256"cursorObject.execute(query,attrValues)dataBase.commit()# disconnecting from serverdataBase.close()
Output:
In the above program, a table named STUDENT is created having attributes NAME, BRANCH, ROLL, SECTION and AGE. Multiple data is inserted into the STUDENT table and then a single query is deleted from the table having the ROLL attribute value 1706256.Example 2: Let us look at another example for queries in a table.
Python# importing required libraryimportmysql.connector# connecting to the databasedataBase=mysql.connector.connect(host="localhost",user="user",passwd="pswrd",database="geeks")# preparing a cursor objectcursorObject=dataBase.cursor()# creating tablestudentRecord="""CREATE TABLE STUDENT ( NAME VARCHAR(20) NOT NULL, BRANCH VARCHAR(50), ROLL INT NOT NULL, SECTION VARCHAR(5), AGE INT)"""# table createdcursorObject.execute(studentRecord)# inserting data into the tablequery="INSERT INTO STUDENT (NAME, BRANCH, ROLL, SECTION, AGE) VALUES (%s,%s,%s,%s,%s)"attrValues=[("Rituraj Saha","Information Technology",1706256,"IT-3",20),("Ritam Barik","Information Technology",1706254,"IT-3",21),("Rishi Kumar","Information Technology",1706253,"IT-3",21)]forvaluesinattrValues:cursorObject.execute(query,values)# deleting querydelete_query="DELETE FROM STUDENT WHERE ROLL =%s"delete_value=(1706256,)cursorObject.execute(delete_query,delete_value)# committing changesdataBase.commit()# disconnecting from serverdataBase.close()Output:
In the above program, another table is created in the geeks database named PHONE_RECORD having attribute NAME and PHONE. Only one column is inserted into the table and then it is deleted using the DELETE statement.