1. Display Data
1.1. Fetchone
1.1.1. Return one record from ResultSet
1.1.1.1. mycursor.execute("select....")
1.1.1.2. mydata=mycursor.fetchone()
1.1.1.3. print(mydata)
1.2. Fetchall
1.2.1. Return all records from Result Set in form of tuple
1.2.1.1. mycursor.execute("select....")
1.2.1.2. mydata=mycursor.fetchall()
1.2.1.3. for row in mydata:
1.2.1.4. print(row)
1.3. Fetchmany
1.3.1. Return n number of records
1.3.1.1. mycursor.execute("select....")
1.3.1.2. mydata=mycursor.fetchmany(n)
1.3.1.3. for row in mydata:
1.3.1.4. print(row)
1.4. Rowcount
1.4.1. Return number of rows retrieved from the cursor
1.4.1.1. mycursor.execute("select....")
1.4.1.2. mydata=mycursor.fetchall()
1.4.1.3. datacount = mycursor.rowcount
1.4.1.4. print("Total row:", datacount)
2. Parameterized Query (Query based on some parameters)
2.1. Concatenation with Query
2.1.1. e.g. query="select * from tblStudent where name="str(studentname)
2.2. String Template with %S formatting
2.2.1. e.g. query="select * from tblStudent where name= %s and roll=%s" %(studentname, rollno,)
2.3. String Template with {} and format function
2.3.1. e.g. query="select * from tblStudent where name= { } and roll={ }" .format (studentname, rollno )
3. Connection between SQL and Python (Database connectivity is the connection and communication b/w an application and database)
3.1. 1. Open Python
3.1.1. Start Python IDE
3.2. 2. import package (mysql.connector)
3.2.1. import mysql.connector as <alias>
3.2.2. e.g. import mysql.connector as sqlcon
3.2.3. or use pymysql
3.3. 3. Opening of Connection to db
3.3.1. Establish connection to MySQL db using connect() function of mysql.connector package.
3.3.1.1. <Connectionobject> =mysql.connector.connect(host=<hostname>, user= <username>, passwd=<password>[,database = <database>])
3.3.1.2. e.g. mycon = sqlcon.connect(host="localhost", user="root", passwd="pass", database="newdb")
3.3.2. Connection is the session between application program and database.
3.4. 4. Creation of Cursor Instance
3.4.1. Special Control Structure to execute query and row wise processing of records
3.4.1.1. <cursorobject> = <connectionobject> .cursor()
3.4.1.2. e.g. mycursor = mycon.cursor()
3.5. 5. Execution of Query and result in resultset
3.5.1. Execute sql query using execute() function
3.5.1.1. <cursorobject>.execute(<sqlquerystring>)
3.5.1.2. e.g. mycursor = execute("select.....)
3.6. 6. Extraction of data from resultset
3.6.1. Result set is logical set of records that are fetched from db by executing SQL query
3.6.1.1. <data>= <cursor>.fetchone() / fetchmany (n) / fetchall ()
3.6.1.2. mydata=mycursor.fetchall()
3.7. 7. Clean up the Environment
3.7.1. Close the connection after all procession using close() function
3.7.1.1. <connectionObject>.close()
3.7.1.2. e.g. mycon.close()
4. Query Using Cursor
4.1. Insert Query
4.1.1. query="insert into......"
4.1.2. mycursor.execute(query)
4.1.3. mycon.commit()
4.2. Update Query
4.2.1. query="update ......"
4.2.2. mycursor.execute(query)
4.2.3. mycon.commit()
4.3. Delete Query
4.3.1. query="delete ......"
4.3.2. mycursor.execute(query)
4.3.3. mycon.commit()