Unit 2 Database Backup and CSV File Handling.pdf

303: Database handling Using Python Unit 2: Database backup and CSV handling SQLite Dump: You can use special commands, which are known as dot-commands to perform various useful database operations. One of these dot-commands is the.dump command that give...

303: Database handling Using Python Unit 2: Database backup and CSV handling SQLite Dump: You can use special commands, which are known as dot-commands to perform various useful database operations. One of these dot-commands is the.dump command that gives you the ability to dump the entire database or tables into a text file. 1) Dump specific table into file: If you want to dump a specific table, you need to specify the table name followed the.dump command. For example, the following command saves the product table to the product.sql file. Dump only one table from the database: Step-1: Open the database from which you want to dump a table. You can open a data base from following statement. Sqlilte3 db1.db Here db1 is a name of the database. Step-2: Write down following step to dump a specific table into file..output c:/sqlite/product.sql.dump product.quit Here, product.sql is name of the file in which you want to dump a table. Page 1  303: Database handling Using Python Dump more than one table from the database: Step-1: Open the database from which you want to dump a table. You can open a data base from following statement. Sqlilte3 db1.db Here db1 is a name of the database. Step-2: Write down following step to dump a specific table into file..output c:/sqlite/product.sql.dump product.dump product_log.quit Here, product.sql is name of the file in which you want to dump a table. You can write multiple.dump table_name if you want to dump more than one table from a database into a single file. Page 2  303: Database handling Using Python 2) Dump only table structure into file: You can dump table structure only using schema command. Dump only one table’s structure from the database: If you want to dump the structures of tables in a database, you use the.schema command. The following commands set the output file to Employee_structure.sql file and save the structures of tables into the Employee_structure.sql file. Page 3  303: Database handling Using Python Step-1: Open the database from which you want to dump a table. You can open a data base from following statement. Sqlilte3 db1.db Here db1 is a name of the database. Step-2: Write down following step to dump a specific table into file..output c:/sqlite/Employee_structure.sql.schema Employee.quit Here, Employee_structure.sql is name of the file in which you want to dump a structure of a table. Dump more than one table’s structure from the database: Step-1: Open the database from which you want to dump a table. You can open a data base from following statement. Sqlilte3 db1.db Here db1 is a name of the database. Step-2: Write down following step to dump a specific table into file. Page 4  303: Database handling Using Python.output c:/sqlite/EMP_DEP_structure.sql.schema Employee.schema Department.quit Here, EMP_DEP_structure.sql is name of the file in which you want to dump a structure of a table. You can add multiple.schema table_name if you want to dump more than one table’s structure. Dump all table’s structure present in a database in to file: Step-1: Open the database from which you want to dump a table. You can open a data base from following statement. Sqlilte3 db1.db Here db1 is a name of the database. Step-2: Write down following step to dump a specific table into file..output c:/sqlite/Schema.sql Page 5  303: Database handling Using Python.schema.quit 3) Dump entire database into a file. To dump a database into a file, you use the.dump command. The.dump command converts the entire structure and data of an SQLite database into a single text file. By default, the.dump command outputs the SQL statements on screen. To issue the output to a file, you use the.output FILENAME command. The following commands specify the output of the dump file to db1.sql and dump the db1 database into the db1.sql file. Step-1: Open the database from which you want to dump a table. You can open a data base from following statement. Sqlilte3 db1.db Page 6  303: Database handling Using Python Here db1 is a name of the database. Step-2: Write down following step to dump an entire database into file..output c:/sqlite/db1.sql.dump.quit 4) Dump data of one or more tables into a file. To dump the data of a table into a text file, you use these steps: Step-1: First, Set the mode to insert using the.mode command as follows:.mode insert Page 7  303: Database handling Using Python From now on, every SELECT statement will issue the result as the INSERT statements instead of pure text data. Step-2: Second, set the output to a text file instead of the default standard output. The following command sets the output file to the data.sql file..output data.sql Step-3: Third, issue the SELECT statements to query data from a table that you want to dump. The following command returns data from the artists table. Select * from Employee; To dump data from other tables, you need to issue the SELECT statements to query data from those tables. Dump data of more than one table into single file: To dump the data of a table into a text file, you use these steps: Step-1: First, Set the mode to insert using the.mode command as follows:.mode insert Page 8  303: Database handling Using Python From now on, every SELECT statement will issue the result as the INSERT statements instead of pure text data. Step-2: Second, set the output to a text file instead of the default standard output. The following command sets the output file to the data.sql file..output data.sql Step-3: Third, issue the SELECT statements to query data from a table that you want to dump. The following command returns data from the artists table. Select * from Employee; Select * from Department; CSV File Handling: Import a CSV file into a table In SQLite we can easily import data from external files like CSV, excel to database tables by using the “.import” command. Page 9  303: Database handling Using Python The syntax of the SQLite “import” command to import data from external files..import file_name table_name The.import command is used to import data from an external file and insert it into the specified table. To import data from the CSV file, you must have to execute command.mode CSV before.import command, and the table must already exist. Now to import data from CSV file first create tabled called “Sample” in the database using the following query statement. Create table Sample ( Roll_no Text Primary key, Name Text ); For this First, use.separator command to change the separator for CSV file. By default there is Colon ( : ) separator, but for CSV file we need Comma( , ) separator. For changing separator use following syntax:.separator , Using this syntax separator will be change from Colon ( : ) to comma ( , ). Before importing CSV file, we have to create CSV file in c:/sqlite folder. Page 10  303: Database handling Using Python SYBCADIV1.csv Once we did with table creation and SYBCADIV1. CSV file, now we will import SYBCADIV1.csv data to Sample table like as shown below..import SYBCADIV1.csv sample Now, check the table data by using following query SELECT * from Sample; Page 11  303: Database handling Using Python Here you can see that our SYBCADIV1.CSV file will feed into out Sample table. Export a CSV file into a table SQLite Export Data from Table to CSV File In SQLite, by using “.output” command we can export data from database tables to CSV or excel external files based on our requirement. Syntax of SQLite Export Command:.output (Filename) Example: We will export “product” table data to product.csv file for that write the query like as shown below. Let’s look at the example of exporting data of product table to product.csv file. This file does not exist. So it will first create and export data into it. To export data from SQLite database to CSV or Excel file we followed few steps those are-- Step-1: To insert table column names in CSV or Excel file we used.header on command..header on Step-2: To return the data in CSV format we used.mode CSV..mode csv Step-3: To send data to CSV file we used.output command..output product.csv Page 12  303: Database handling Using Python Step-4: SELECT statement to export data from the required table. SELECT * from product; You can find the product.csv file into c:/sqlite folder. Product.Csv file Page 13 303: Database handling Using Python Page 14