(ii) Kabir wants to write a program in Python to insert the following record in the table named Student in MYSQL database, SCHOOL:
rno(Roll number )- integer
name(Name) - string
DOB (Date of birth) – Date
Fee – float
Note the following to establish connectivity between Python and
Username - root
Password - tiger
Host - localhost
The values of fields rno, name, DOB and fee has to be accepted from the user. Help Kabir to write the program in Python.
Answer by student
Detailed answer by teachoo
The steps involved in writing the code are:
- Step 1: Import mysql.connector module to interface Python with MySQL. This module provides methods and classes to connect to a MySQL database and execute SQL commands.
- Step 2: Connect to MySQL database using root username and tiger password. The connect() method returns a connection object that can be used to access the database. The host, user and password parameters specify the host name or IP address of the MySQL server, the user name for logging into the MySQL server and the password for logging into the MySQL server respectively.
- Step 3: Create a cursor object to execute SQL commands. The cursor() method returns a cursor object that can be used to execute SQL queries and fetch results.
- Step 4: Use the SCHOOL database that contains the Student table. The execute() method executes an SQL statement on the database. The statement “USE SCHOOL” tells the database system to use the SCHOOL database for further operations.
- Step 5: Accept the values of fields rno, name, DOB and fee from the user using the input() function. The input() function takes a string as an argument and returns the user input as a string. The int() and float() functions convert the user input to integer and float data types respectively. The rno, name, DOB and fee variables store the values of fields rno, name, DOB and fee respectively.
- Step 6: Insert the record into the Student table using a direct query. The query is a string that contains the SQL statement to insert a record into the Student table. The values of fields rno, name, DOB and fee are concatenated with the query using the + operator. The str() function converts the integer and float values to strings. The name and DOB values are enclosed in single quotes as they are string values. The execute() method executes the query on the database.
- Step 7: Commit the changes to the database using the commit() method of the connection object. This saves the changes permanently in the database and closes the transaction.
- Step 8: Print a success message using the rowcount attribute of the cursor object. The rowcount attribute returns the number of rows affected by the last executed statement. The print() function prints how many records were inserted.
So, the Python program insert a record in a table in MySQL database is