Page 102 - Informatics_Practices_Fliipbook_Class12
P. 102

from sqlalchemy import create_engine
                engine = create_engine("mysql+pymysql://root:password@localhost:3306/NCERT")
                conn = engine.connect()
                df.to_sql('Product_table',conn,index=False)     #data transferred to sql
                conn.close()
                 Explanation:
                  i.   Importing  the  create_engine  function  from  the  SQLAlchemy  library,  which  is  commonly  used  for  database
                   interaction.
                  ii.   engine = create_engine("mysql+pymysql://root:password@localhost:3306/NCERT"): Creates
                   a MySQL database engine using SQLAlchemy. It specifies the database type (mysql+pymysql), username (root),
                   password (password), host (localhost), port (3306), and database name (NCERT).
                 iii.   conn = engine.connect(): Establishes a connection to the MySQL database using the previously defined
                   engine.
                 iv.   df.to_sql('Product_table', conn, index=False): Transfers the DataFrame df to a MySQL table
                   named 'Product_table' in the database specified by the connection. The index = False parameter indicates that the
                   DataFrame's index should not be stored as a separate column in the database table.
          14.  Write the python statement for the following question on the basis of given dataset:

                       Name      Degree   Score
                0    Aparna        MBA     90.0
                1    Pankaj        BCA      NaN
                2      Ram       M.Tech    80.0
                3    Ramesh        MBA     98.0

                4    Naveen        NaN     97.0
                5   Krrishnav      BCA     78.0
                6    Bhawna        MBA     89.0

              a.  To create the above DataFrame.
              b.  To print the Degree and maximum marks in each stream.
              c.  To fill the NaN with 76.
              d.  To set the index to Name.
              e.  To display the name and degree wise average marks of each student.
              f.  To count the number of students in MBA.
              g.  To print the mode marks BCA.

         Ans.  a. import pandas as pd
                # Given DataFrame
                myDict = {"Name": ["Aparna", "Pankaj", "Ram", "Ramesh", "Naveen", "Krrishav", "Bhawna"],
                     "Degree": ["MBA", "BCA", "M.Tech", "MBA", np.nan, "BCA", "MBA"],
                     "Score": [90.0, np.nan, 80.0, 98.0, 97.0, 78.0, 89.0]}
                df = pd.DataFrame(myDict)
                print(df)
                print("\n")
              b.  maxMarksEachStream = df.groupby("Degree")["Score"].max()
                print("Degree and maximum marks in each stream:")
                print(maxMarksEachStream)
                print("\n")

          88   Touchpad Informatics Practices-XII
   97   98   99   100   101   102   103   104   105   106   107