my_cursor = my_conn.cursor()
my_cursor.execute("SELECT * FROM student")
my_result = my_cursor.fetchone() # we get a tuple
#print each cell ( column ) in a line
print(my_result)
#Print each colomn in different lines.
for x in my_result:
print(x)
We defined my_cursor in our database connection string. We have used our student table.
print(type(my_result)) # Output is <class 'tuple'>
(1, 'John Deo', 'Four', 75, 'female')
1
John Deo
Four
75
female
This is our first record of the student table.
my_cursor = my_conn.cursor()
my_cursor.execute("SELECT * FROM student")
my_result = my_cursor.fetchone()
print("Student id = ",my_result[0])
print("Student Name = ",my_result[1])
print("Student Class = ",my_result[2])
print("Student Mark = ",my_result[3])
print("Student gender = ",my_result[4])
Output is here
Student id = 1
Student Name = John Deo
Student Class = Four
Student Mark = 75
Student gender = female
By adding this code at the end of the above secript ,we can display the next record
my_result = my_cursor.fetchone()
print("Student id = ",my_result[0])
print("Student Name = ",my_result[1])
print("Student Class = ",my_result[2])
print("Student Mark = ",my_result[3])
print("Student gender = ",my_result[4])
We can use the same technique to iterate through all the records and display
my_cursor = my_conn.cursor()
my_cursor.execute("SELECT * FROM student")
my_result = my_cursor.fetchone()
while my_result is not None:
print(my_result)
my_result = my_cursor.fetchone()
This will display all records
(1, 'John Deo', 'Four', 75, 'female')
(2, 'Max Ruin', 'Three', 85, 'male')
(3, 'Arnold', 'Three', 55, 'male')
- - - - - -
- - - - - -
We can use cursor to iterate through the records
my_cursor = my_conn.cursor()
my_cursor.execute("SELECT * FROM student")
for my_result in my_cursor:
print(my_result)
Output will display all the records as above.
my_cursor = my_conn.cursor()
my_cursor.execute("SELECT * FROM student")
my_result=my_cursor.fetchall()
for row in my_result:
print(row)
The output is same as above , displaying all the records.
q="SELECT id,name FROM student WHERE class='Four' "
my_cursor=my_conn.execute(q)
my_result=my_cursor.fetchall()
my_ids = [row[0] for row in my_result] # All id as list
my_names = [row[1] for row in my_result] # All names as list
By changing the query, we can get all the classes with number of students in each class as list.
q="SELECT class,count(*) as no FROM student GROUP BY class"
q="SELECT id,name FROM student WHERE class='Four' "
my_cursor=my_conn.execute(q)
columns=list(my_cursor.keys()) # column names as list
Output
['id', 'name']
my_cursor = my_conn.cursor()
my_cursor.execute("SELECT * FROM student")
my_result_top=my_cursor.fetchmany(size=3)
#my_result=my_cursor.fetchall()
for row in my_result_top:
print(row)
Output is here
(1, 'John Deo', 'Four', 75, 'female')
(2, 'Max Ruin', 'Three', 85, 'male')
(3, 'Arnold', 'Three', 55, 'male')
If you don't use size=3 then fetchmany will return one row of record only.
my_cursor = my_conn.cursor()
my_cursor.execute("SELECT * FROM student")
my_result_top=my_cursor.fetchmany()
for row in my_result_top:
print(row)
Output is here
(1, 'John Deo', 'Four', 75, 'female')
By using both fetchall() and fetchmaney()
my_cursor = my_conn.cursor()
my_cursor.execute("SELECT * FROM student")
my_result_top=my_cursor.fetchmany(size=3)
for row in my_result_top:
print(row)
my_result=my_cursor.fetchall()
for row in my_result:
print(row)
Output is here ( displayes all records )
(1, 'John Deo', 'Four', 75, 'female')
(2, 'Max Ruin', 'Three', 85, 'male')
(3, 'Arnold', 'Three', 55, 'male')
(4, 'Krish Star', 'Four', 60, 'female')
(5, 'John Mike', 'Four', 60, 'female')
(6, 'Alex John', 'Four', 55, 'male')
- - - - - -
- - - - - -
Note that after using fetchmany(size=3), fetchall() returns balance rows starting from 4th record.
You can see we are not using the SQL again, so it is evident that after retrieving all data from MySQL ( by using the SQL ) records are stored in Python memory and we only retrieve part of the available records by using fetchmany() and rest of the records are collected through fetchall() from Python memory and not from database again.
my_cursor = my_conn.cursor()
my_cursor.execute("SELECT * FROM student WHERE id=7")
my_result=my_cursor.fetchall()
for row in my_result:
print(row)
This will return only one record. We can change the Query part like this
my_cursor.execute("SELECT * FROM student LIMIT 0,10")
This will return 10 records starting from first record.( What is LIMIT Query ? )
from sqlalchemy import create_engine
my_conn = create_engine("mysql+mysqldb://id:pw@localhost/my_tutorial")
query = "SELECT * FROM student LIMIT 0,5"
my_data = list(my_conn.execute(query)) # SQLAlchem engine result set
my_dict = {} # Create an empty dictionary
my_list = [] # Create an empty list
for row in my_data:
my_dict[[row][0][0]] = row # id as key
my_list.append(row[1]) # name as list
print(my_dict)
print(my_list)
# Print the other values for matching Name
for i, j in my_dict.items():
if j[1] == "Arnold":
print(i, j[0], j[1], j[2])