Q: Question 13In pandas.read_sql(<A>, ), <A> isconnection nametable nameSQL query stringdatabase name

SQL query stringReason — The <A> parameter in pandas.read_sql(<A>, ) represents the SQL query string used to fetch data from the database.

Q: Question 14In pandas.read_sql(<A>, ), isconnection nametable nameSQL query stringdatabase name

connection nameReason — The parameter in pandas.read_sql(<A>, ) represents the connection object or the database connection.

Question 1

Question 8To skip first 5 rows of CSV file, which argument will you give in read_csv() ?skiprows = 5skip_rows = 5skip = 5noread = 5

Accepted Answer

skiprows = 5Reason — The skiprows = 5 argument in the read_csv() function is used to skip the first 5 rows of the CSV file and start reading from the 6th row onwards.

Question 2

Question 9To skip 1st, 3rd and 5th row of CSV file, which argument will you give in read_csv() ?skiprows = 1 | 13 | 5skiprows = [1, 5, 1]skiprows = [1, 3, 5]any of these

Accepted Answer

skiprows = [1, 3, 5]Reason — The argument to skip 1st, 3rd and 5th row of CSV file in read_csv() is skiprows = [1, 3, 5].

Source: This question is from Importing/Exporting Data between CSV Files/MySQL and Pandas, Informatics Practices &mdash; Class 12, CBSE Board.
Key Concepts CoveredThis question tests your understanding of the following concepts from the chapter Importing/Exporting Data between CSV Files/MySQL and Pandas: Question, Skip, Row, Csv, File, Argument. These are fundamental topics in Informatics Practices that students are expected to master as part of the CBSE Class 12 curriculum.A thorough understanding of these concepts will help you answer similar questions confidently in your CBSE examinations. These topics are frequently tested in both objective and subjective sections of Informatics Practices papers. We recommend revising the relevant section of your textbook alongside practising these solved examples to build a strong foundation.
How to Approach MCQsMultiple choice questions test your ability to quickly recall facts and apply concepts. Read all options carefully before selecting your answer. Use the process of elimination to rule out clearly incorrect options first. If two options seem similar, look for the subtle difference that makes one more accurate. For numerical MCQs, verify your answer by substituting it back into the problem.
Key Points to RememberRead the question carefully and identify all parts before answering.
Use the terminology specific to this subject and chapter.
Review the textbook content for this chapter before attempting questions.
Practice writing concise, well-structured answers within time limits.
Related Questions from Importing/Exporting Data between CSV Files/MySQL and PandasQ1: Question 1 Predict the output of following code fragments one by one. For every next code fragment,...
Q10: Question 10 While reading from a CSV file, to use a column&#x27;s values as index labels, argument given...
Q11: Question 11 If query is a string storing an SQL statement. Write statements so that the data is...
Q12: Question 12 Which of the following libraries let you establish a connection with a MySQL database...
Q13: Question 13 In pandas.read_sql( , ), is connection name table name SQL query string database name
Practice more questions from Importing/Exporting Data between CSV Files/MySQL and Pandas &mdash; Informatics Practices, Class 12 CBSE

Question 3

Question 10While reading from a CSV file, to use a column's values as index labels, argument given in read_CSV() is :indexindex_colindex_valuesindex_label

Accepted Answer

index_colReason — The argument given in read_csv() to use a column's values as index labels is index_col. By specifying index_col with the name of the desired column, pandas will use the values from that column as the index labels for the DataFrame.

Question 4

Question 12Which of the following libraries let you establish a connection with a MySQL database from within Python ?mysql.connectorPymysql, sqlalchemypandasnumpy

Accepted Answer

mysql.connector, Pymysql, sqlalchemyReason — The libraries mysql.connector, PyMySQL, and SQLAlchemy all enable to establish connections with MySQL databases from within Python.

Question 5

Question 13In pandas.read_sql(, ), isconnection nametable nameSQL query stringdatabase name

Accepted Answer

SQL query stringReason — The parameter in pandas.read_sql(, ) represents the SQL query string used to fetch data from the database.

Question 6

Question 14In pandas.read_sql(, ), isconnection nametable nameSQL query stringdatabase name

Accepted Answer

connection nameReason — The parameter in pandas.read_sql(, ) represents the connection object or the database connection.

Question 7

Question 15To suppress the creation of a column for index labels of a DataFrame, ............... argument is specified in to_sql().if_exists = Falseindex = Falseindex = Trueif_exists = True

Accepted Answer

index = FalseReason — The index = False argument in to_sql() is used to suppress the creation of a column for index labels of a DataFrame. This means that the index labels will not be included as a separate column in the database table.

Question 8

Question 16To append the content of DataFrame in a table of MySQL, ............... argument is used in to_sql().if_exists = "Add"if_exists = "append"if_exists = Addif_exists = append

Accepted Answer

if_exists = "append"Reason — The if_exists = "append" argument in to_sql() is used to append the content of a DataFrame to an existing table in MySQL. This ensures that the data from the DataFrame is added to the specified table without overwriting or deleting any existing data.

Question 9

Question 11If query is a string storing an SQL statement. Write statements so that the data is fetched based on query from SQL database Mydata.

Accepted Answer

Let query store the following SQL statement:query = "SELECT * FROM EMPLOYEE WHERE department = 'Human Resource'"import pandas as pd
import mysql.connector as sqltor
mycon = sqltor.connect(host = "localhost",
                        user = "root", 
                        passwd = "MyPass", 
                        database = "Mydata")      
query = "SELECT * FROM EMPLOYEE WHERE department = 'Human Resource'"
mdf = pd.read_sql(query, mycon)
print(mdf)
mycon.close()

Question 10

Question 1Predict the output of following code fragments one by one. For every next code fragment, consider that the changes by previous code fragment are in place. That is, for code fragment (b), changes made by code fragment (a) are persisting ; for (c), changes by (a) and (b) are persisting and so on.(a)import pandas as pd
columns=['2015', '2016', '2017', '2018']
index=['Messi', 'Ronaldo', 'Neymar', 'Hazard']
df = pd.DataFrame(columns = columns, index = index)
print(df)
df.to_csv("c:\one.csv")(b)df['2015']['Messi'] = 12 
df['2016']['Ronaldo'] = 11 
df['2017']['Neymar'] = 8 
df['2018']['Hazard'] = 16 
print(df)
df.to_csv("c:\two.csv", sep = '@')(c)new_df = pd.read_csv('c:\one.csv', index_col = 0) 
print(new_df)(d)new_df = pd.read_csv('c:\one.csv') 
print(new_df)(e)new_df = pd.read_csv('c:\two.csv') 
print(new_df)(f)new_df = pd.read_csv('c:\two.csv', sep = '@')
print(new_df)

Accepted Answer

(a)Output        2015 2016 2017 2018
Messi    NaN  NaN  NaN  NaN
Ronaldo  NaN  NaN  NaN  NaN
Neymar   NaN  NaN  NaN  NaN
Hazard   NaN  NaN  NaN  NaN
ExplanationThe DataFrame df is created with defined columns and index labels. It is then printed, showing NaN values since no data is provided. Afterwards, df is written to a CSV file named one.csv.The contents of the file one.csv are as follows:,2015,2016,2017,2018
Messi,,,,
Ronaldo,,,,
Neymar,,,,
Hazard,,,,
(b)Output        2015 2016 2017 2018
Messi     12  NaN  NaN  NaN
Ronaldo  NaN   11  NaN  NaN
Neymar   NaN  NaN    8  NaN
Hazard   NaN  NaN  NaN   16
ExplanationThe code assigns values to specific cells in the DataFrame df, such as setting the value of 'Messi' in the '2015' column to 12 and so on. After printing the modified DataFrame, it is saved to a CSV file named two.csv using '@' as the separator.The contents of the file two.csv are as follows:@2015@2016@2017@2018
Messi@12@@@
Ronaldo@@11@@
Neymar@@@8@
Hazard@@@@16
(c)Output         2015  2016  2017  2018
Messi     NaN   NaN   NaN   NaN
Ronaldo   NaN   NaN   NaN   NaN
Neymar    NaN   NaN   NaN   NaN
Hazard    NaN   NaN   NaN   NaN
ExplanationThe code reads the CSV file named one.csv into a new DataFrame new_df. The parameter index_col = 0 means the first column of the CSV file will be used as the index. It then prints the contents of new_df. Since the CSV file has an empty DataFrame, the new_df DataFrame will be empty as well.The contents of the file one.csv are as follows:,2015,2016,2017,2018
Messi,,,,
Ronaldo,,,,
Neymar,,,,
Hazard,,,,
(d)Output  Unnamed: 0  2015  2016  2017  2018
0      Messi   NaN   NaN   NaN   NaN
1    Ronaldo   NaN   NaN   NaN   NaN
2     Neymar   NaN   NaN   NaN   NaN
3     Hazard   NaN   NaN   NaN   NaN
ExplanationThe code used the pandas read_csv() function to extract data from one.csv file. The retrieved data is then stored in a DataFrame called new_df and then it is printed. Since the index_col parameter is not specified, the DataFrame is using default numerical indexes. The DataFrame displays "Unnamed: 0" as the header for the first column because the CSV file doesn't have a header row.The contents of the file one.csv are as follows:,2015,2016,2017,2018
Messi,,,,
Ronaldo,,,,
Neymar,,,,
Hazard,,,,
(e)Output  @2015@2016@2017@2018
0          Messi@12@@@
1        Ronaldo@@11@@
2          Neymar@@@8@
3         Hazard@@@@16
ExplanationThe code used the pandas read_csv() function to extract data from the two.csv file. The retrieved data is then stored in a DataFrame called new_df and printed. However, if the actual separator in the two.csv file is "@" instead of the default comma (,), then the code without specifying the sep parameter, will not parse the data correctly. It will assume that the data is comma-separated and will not display the correct structure of the data in the DataFrame.The contents of the file two.csv are as follows:@2015@2016@2017@2018
Messi@12@@@
Ronaldo@@11@@
Neymar@@@8@
Hazard@@@@16
(f)Output  Unnamed: 0  2015  2016  2017  2018
0      Messi  12.0   NaN   NaN   NaN
1    Ronaldo   NaN  11.0   NaN   NaN
2     Neymar   NaN   NaN   8.0   NaN
3     Hazard   NaN   NaN   NaN  16.0
ExplanationThe code extracts data from the two.csv file using the read_csv() function and assigns it to the new_df DataFrame. It then prints new_df. The sep parameter is used to ensure that the DataFrame is correctly parsed, and default indexes are used since index_col is not specified. The term 'Unnamed' is used because a header for the first column is not mentioned in the CSV file.The contents of the file two.csv are as follows:@2015@2016@2017@2018
Messi@12@@@
Ronaldo@@11@@
Neymar@@@8@
Hazard@@@@16

Question 11

Question 5What is the difference between following two statements ?(i)df.to_sql('houses', con = conn, if_exists = 'replace')
(ii)df.to_sql('houses', con = conn, if_exists = 'replace', index = False)

Accepted Answer

The difference between the two statements is whether the DataFrame's index is included as a separate column in the resulting SQL table. By default, when we use to_sql() without specifying the index parameter, index = True is assumed, meaning that the DataFrame's index will be included in the SQL table, as in the first statement. Setting index = False explicitly excludes the DataFrame's index from being saved as a separate column in the SQL table, as in the second statement.

Question 12

Question 6Consider following code when conn is the name of established connection to MySQL database.Cars = {'Brand': ['Alto', 'Zen', 'City', 'Kia'],
        'Price': [22000, 25000, 27000, 35000]}
df = pd.DataFrame(Cars, columns= ['Brand', 'Price']) 
df.to_sql('CARS', conn, if_exists = 'replace', index = False)What will be the output of following query if executed on MySQL ?SELECT * from CARS ;

Accepted Answer

Output+-------+-------+
| Brand | Price |
+-------+-------+
| Alto  | 22000 |
| Zen   | 25000 |
| City  | 27000 |
| Kia   | 35000 |
+-------+-------+
ExplanationThe code initializes a DataFrame df. It then writes this data to an SQL database table named 'CARS' using to_sql(). The SQL query SELECT * FROM CARS; retrieves all columns and rows from the 'CARS' table.

Question 13

Question 7Consider following code when conn is the name of established connection to MySQL database.sql = SELECT * from Sales where zone = "central"; 
df = pandas.read_sql(sql, conn)
df.head()What will be stored in df ?

Accepted Answer

The DataFrame df includes all the columns where 'zone' equals 'central'.ExplanationThe code executes an SQL query to select all columns (*) from the "Sales" table where the "zone" column equals to "central". It then reads the results of the query into a pandas DataFrame df using pandas.read_sql(). Then, it returns the first five rows of df using df.head().

Question 14

Question 2Write a program to read data from a CSV file where separator character is '@'. Make sure that :the top row is used as data, not as column headers.only 20 rows are read into DataFrame.

Accepted Answer

Let the contents of the file bike.csv be the following:Brand@Price
Honda@2500
Yamaha@2800
Suzuki@2100
Kawasaki@3200
Ducati@3500
BMW@4500
Harley-Davidson@5500
KTM@4000
Triumph@5300
Aprilia@4800
Indian@5700
Royal Enfield@3000
MV Agusta@4200
Moto Guzzi@4900
Victory@5600
Benelli@3900
Husqvarna@4800
Zero Motorcycles@6500
Energica@7500
Norton@5900
The program to read data from a CSV file is as follows:import pandas as pd
d = pd.read_csv('one.csv', sep = '@', header = None, nrows = 20)
print(d)Output                   0      1
0              Brand  Price
1              Honda   2500
2             Yamaha   2800
3             Suzuki   2100
4           Kawasaki   3200
5             Ducati   3500
6                BMW   4500
7    Harley-Davidson   5500
8                KTM   4000
9            Triumph   5300
10           Aprilia   4800
11            Indian   5700
12     Royal Enfield   3000
13         MV Agusta   4200
14        Moto Guzzi   4900
15           Victory   5600
16           Benelli   3900
17         Husqvarna   4800
18  Zero Motorcycles   6500
19          Energica   7500

Question 15

Question 3Write a program to get following data in two DataFrames :df 1:Roll noName1ABC2DEFdf2:Roll noMarks 1Marks 2Marks 317080752606570Store these DataFrames as two separate tables in the same database.Solutionimport pandas as pd
from sqlalchemy import create_engine
import pymysql
data1 = {'Roll no': [1, 2], 'Name': ['ABC', 'DEF']}
data2 = {'Roll no': [1, 2], 'Marks1' : [70, 60], 'Marks2': [80, 65],'Marks3' : [75, 70]}
df1 = pd.DataFrame(data1)
df2 = pd.DataFrame(data2)
engine = create_engine('mysql+pymysql://root:Mypass@localhost/database')
mycon = engine.connect()
df1.to_sql('first', mycon, if_exists = 'replace', index = False)
df2.to_sql('second', mycon, if_exists = 'replace', index = False)OutputFirst table:

+---------+------+
| Roll no | Name |
+---------+------+
|       1 | ABC  |
|       2 | DEF  |
+---------+------+

Second table:

+---------+--------+--------+--------+
| Roll no | Marks1 | Marks2 | Marks3 |
+---------+--------+--------+--------+
|       1 |     70 |     80 |     75 |
|       2 |     60 |     65 |     70 |
+---------+--------+--------+--------+

Accepted Answer

importpandasaspdfromsqlalchemyimportcreate_engineimportpymysqldata1={'Roll no': [1,2],'Name': ['ABC','DEF']}data2={'Roll no': [1,2],'Marks1': [70,60],'Marks2': [80,65],'Marks3': [75,70]}df1=pd.DataFrame(data1)df2=pd.DataFrame(data2)engine=create_engine('mysql+pymysql://root:Mypass@localhost/database')mycon=engine.connect()df1.to_sql('first',mycon,if_exists='replace',index=False)df2.to_sql('second',mycon,if_exists='replace',index=False)OutputFirst table:+---------+------+| Roll no | Name |+---------+------+|       1 | ABC  ||       2 | DEF  |+---------+------+Second table:+---------+--------+--------+--------+| Roll no | Marks1 | Marks2 | Marks3 |+---------+--------+--------+--------+|       1 |     70 |     80 |     75 ||       2 |     60 |     65 |     70 |+---------+--------+--------+--------+

Question 16

Question 4You have a database on MySQL namely school having three tables in it — Student, Subject, Teacher. Write a program to store these tables in three DataFrames.Solutionimport pandas as pd
import mysql.connector as sqltor
mycon = sqltor.connect(host = "localhost",
                       user = "root",
                       passwd = "mypass",
                       database = "school")
mdf = pd.read_sql("SELECT * FROM student;", mycon)
ndf = pd.read_sql("SELECT * FROM subject;", mycon)
ldf = pd.read_sql("SELECT * FROM teacher;", mycon)
print("Student dataframe:
")
print(mdf)
print("Subject dataframe:
")
print(ndf)
print("Teacher dataframe:
")
print(ldf)OutputStudent dataframe:

NO     NAME  AGE DEPARTMENT  FEE SEX
0   1   PANKAJ   24   COMPUTER  120   M
1   2  SHALINI   21    HISTORY  200   F
2   3   SANJAY   22      HINDI  300   M
3   4    SUDHA   25    HISTORY  400   F
4   5   ANANYA   23   COMPUTER  450   F

Subject dataframe:

Subject_id     name  Teacher_id
0         101     math         201
1         102  science         202
2         103  english         203
3         104    hindi         204

Teacher dataframe:

No      Name  Age Department  Salary Sex  Dateofjoin
0   1     Jugal   34   Computer   12000   M  1997-01-10
1   2  Sharmila   31    History   20000   F  1998-03-24
2   3   Sandeep   32      Maths   30000   M  1996-12-12
3   4  Sangeeta   35    History   40000   F  1999-07-01
4   5    Rakesh   42      Maths   25000   M  1997-09-05
5   6     Shyam   50    History   30000   M  1998-06-27
6   7   Shiv Om   44   Computer   21000   M  1997-02-25
7   8  Shalakha   33      Maths   20000   F  1997-07-31

Accepted Answer

importpandasaspdimportmysql.connectorassqltormycon=sqltor.connect(host="localhost",user="root",passwd="mypass",database="school")mdf=pd.read_sql("SELECT * FROM student;",mycon)ndf=pd.read_sql("SELECT * FROM subject;",mycon)ldf=pd.read_sql("SELECT * FROM teacher;",mycon)print("Student dataframe:
")print(mdf)print("Subject dataframe:
")print(ndf)print("Teacher dataframe:
")print(ldf)OutputStudent dataframe:   NO     NAME  AGE DEPARTMENT  FEE SEX0   1   PANKAJ   24   COMPUTER  120   M1   2  SHALINI   21    HISTORY  200   F2   3   SANJAY   22      HINDI  300   M3   4    SUDHA   25    HISTORY  400   F4   5   ANANYA   23   COMPUTER  450   FSubject dataframe:   Subject_id     name  Teacher_id0         101     math         2011         102  science         2022         103  english         2033         104    hindi         204Teacher dataframe:   No      Name  Age Department  Salary Sex  Dateofjoin0   1     Jugal   34   Computer   12000   M  1997-01-101   2  Sharmila   31    History   20000   F  1998-03-242   3   Sandeep   32      Maths   30000   M  1996-12-123   4  Sangeeta   35    History   40000   F  1999-07-014   5    Rakesh   42      Maths   25000   M  1997-09-055   6     Shyam   50    History   30000   M  1998-06-276   7   Shiv Om   44   Computer   21000   M  1997-02-257   8  Shalakha   33      Maths   20000   F  1997-07-31

Importing/Exporting Data between CSV Files/MySQL and Pandas

Multiple Choice Questions

Type A Short Answer Questionsconceptual Questions

Type B Application Based Questions

Type C Programming Practiceknowledge Based Questions