Displaying data by using connect(), cursor(), execute(), commit(), fetchone(), fetchall(), rowcount means using python code to retrieve and show the data from the database tables using these methods and attributes of the connection and cursor objects.
- To do this, you need to have a connection object and a cursor object that represent the connection to the database and the ability to execute queries, respectively.
For example, if you are using MySQL, you can write:
import mysql.connector
db_connection = mysql.connector.connect(
host="localhost",
user="root",
passwd="admin",
database="teachoo"
)
my_database = db_connection.cursor()
- Then, you need to define the SQL query that you want to execute, using %s as a placeholder for the values you want to select.
For example, to select all the records from a table named GEEKPORTALLOGIN , you can write:
sql_query = “SELECT * FROM TEACHOOLOGIN”
- Next, you need to execute the query using the execute() method of the cursor object, passing the query as an argument.
For example, to execute the select query, you can write:
my_database.execute(sql_query)
- After executing the query, you need to fetch the data from the cursor object using one of these methods: fetchone() , fetchall() , or fetchmany() .
For example, to fetch all the records as a list of tuples, you can write:
result = my_database.fetchall()
- The fetchone() method returns one record at a time , while the fetchall() method returns all the records as a list of tuples. The fetchmany() method returns the number of rows specified by an argument. If no more rows are available, these methods return an empty list or None.
- You can also use the rowcount attribute of the cursor object to get the number of rows affected by the query.
- To display the data , you can iterate over the result using a for loop and print each row or column value.
For example, to display all the records from the TEACHOOLOGIN table, you can write:
for row in result:
print(row)
