Page 3 - Interface Python With SQL

mycursor.execute("create table student(Adm int(5) primary key, Name varchar(35), DOB date,
stream varchar(15))")
Extract data from result set:
1) Can be through simple coding
2) Can be through functions fetchall(), fetchone(), fetchmany or rowcount()

4) Program to display records of a table
import sys
import mysql.connector
from mysql.connector import Error
try:
db = mysql.connector.connect(host='localhost',\
database='practice', user='root', \
password='1234')
if db.is_connected():
print('Connected to MySQL database')
except Error as e:
print(e)
sys.exit() # to kill program execution
pysql=db.cursor()
pysql.execute("select * from employee")
for x in pysql:
print(x)

Extracting data from Resultset
After execution of SQL query, result of the query is available in the cursor object in the form of
resultset. Data can also be extracted by any of the following fetch…() functions form resultset.
i) <data> = <cursor>.fetchall(), will return all the records in tuple form
ii) <data> = <cursor>.fetchone(), will return one record at a time from the result set
iii) <data> = <cursor>.fetchmany(<n>), will return n number of records from the resultset.
iv) <variable> = <cursor>.rowcount, returns the number of rows retrieved from the result set so
far.

fetchall()
import mysql.connector
db = mysql.connector.connect(host='localhost',\
database='practice',user='root',password='1234')
pysql=db.cursor()
pysql.execute("select * from employee")
data=pysql.fetchall()
for row in data:
print(row)
count=pysql.rowcount
print(count, "number of rows")

fetchmany()
import mysql.connector
db = mysql.connector.connect(host='localhost',\