Data Manipulation Through SQL PDF

www.tntextbooks.in CHAPTER 15 Unit V DATA MANIPULATION THROUGH SQL Learning Objectives...

www.tntextbooks.in CHAPTER 15 Unit V DATA MANIPULATION THROUGH SQL Learning Objectives After the completion of this chapter, the student will be able to write Python script to Create a table and to add new rows in the database. Update and Delete record in a table Query the table Write the Query in a CSV file 15.1 Introduction A database is an organized collection of data. The term "database" can both refer to the data themselves or to the database management system. The Database management system is a application software for the interaction between users and the databases. Users don't have to be human users. They can be other programs and applications as well. We will learn how Python program can interact as a user of an SQL database. 15.2 SQLite SQLite is a simple relational database system, which saves its data in regular data files within internal memory of the computer. It is designed to be embedded in applications, instead of using a separate database server program such as MySQLor Oracle. SQLite is fast, rigorously tested, and flexible, making it easier to work. Python has a native library for SQLite. To use SQLite, import sqlite3 Step 1 Step 2 create a connection using connect () method and pass the name of the database File Step 3 Set the cursor object cursor = connection. cursor () Connecting to a database in step2 means passing the name of the database to be accessed. If the database already exists the connection will open the same. Otherwise, Python will open a new database file with the specified name. XII Std Computer Science 278 12th Computer Science_EM Chapter 15.indd 278 23-12-2022 15:33:41  www.tntextbooks.in Cursor in step 3: is a control structure used to traverse and fetch the records of the database. Cursor has a major role in working with Python. All the commands will be executed using cursor object only. To create a table in the database, create an object and write the SQL command in it. Example:- sql_comm = "SQL statement" For executing the command use the cursor method and pass the required sql command as a parameter. Many number of commands can be stored in the sql_comm and can be executed one after other. Any changes made in the values of the record should be saved by the commend "Commit" before closing the "Table connection". 15.3 Creating a Database using SQLite The following example explains how a connection to be made to a database through Python sqlite3 # Python code to demonstrate table creation and insertions with SQL # importing module import sqlite3 # connecting to the database connection = sqlite3.connect ("Academy.db") # cursor cursor = connection.cursor() In the above example a database with the name "Academy" would be created. It's similar to the sql command "CREATE DATABASE Academy;" to SQL server."sqlite3.connect ('Academy.db')" is again used in some program, "connect" command just opens the already created database. 15.3.1 Creating a Table After having created an empty database, you will most probably add one or more tables to this database. The SQL syntax for creating a table "Student" in the database "Academy" looks like as follows : CREATE TABLE Student ( Rollno INTEGER, Sname VARCHAR(20), Grade CHAR(1), gender CHAR(1), Average float(5, 2), birth_date DATE, PRIMARY KEY (Rollno) ); This is the way, somebody might do it on a SQL command shell. Of course, we want to do this directly from Python. To be capable to send a command to "SQL", or SQLite, we need a 279 Data Manipulation Through SQL 12th Computer Science_EM Chapter 15.indd 279 23-12-2022 15:33:41  www.tntextbooks.in cursor object. Usually, a cursor in SQL and databases is a control structure to traverse over the records in a database. So it's used for the fetching of the results. Note Cursor is used for performing all SQL commands. The cursor object is created by calling the cursor() method of connection. The cursor is used to traverse the records from the result set. You can define a SQL command with a triple quoted string in Python. The reason behind the triple quotes is sometime the values in the table might contain single or double quotes. Example 15.3.1 sql_command = """ CREATE TABLE Student ( Rollno INTEGER PRIMARY KEY , Sname VARCHAR(20), Grade CHAR(1), gender CHAR(1), Average DECIMAL(5,2), birth_date DATE);""" In the above example the Rollno field as "INTEGER PRIMARY KEY" A column which is labeled like this will be automatically auto-incremented in SQLite3. To put it in other words: If a column of a table is declared to be an INTEGER PRIMARY KEY, then whenever a NULL will be used as an input for this column, the NULL will be automatically converted into an integer which will one larger than the highest value so far used in that column. If the table is empty, the value 1 will be used. 15.3.2 Adding Records To populate (add record) the table "INSERT" command is passed to SQLite. “execute” method executes the SQL command to perform some action. The following example 15.3.2 is a complete working example. To run the program you should uncomment the "DROP TABLE" line in the SQL command, if the program has been executed already. XII Std Computer Science 280 12th Computer Science_EM Chapter 15.indd 280 23-12-2022 15:33:41