1 / 11

SQL Data Manipulation with Python Scripting

Learn how to create tables, insert new rows, update and delete records, and query tables using SQL commands in Python. Introduction to SQLite database and its advantages. Step-by-step guide to creating a database and table using Python's sqlite3 module.

wwilhelm
Download Presentation

SQL Data Manipulation with Python Scripting

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. CHAPTER 15DATA MANIPULATION THROUGH SQL To write python script to create a table and add new rows, update & delete records, query table www.Padasalai.Net Uma, MCA

  2. Data Manipulation language(DML) adding(inserting) Removing (deleting) Modifying (updating) data in a database. SQL COMMANDS: • Insert • Update • Delete Uma, MCA

  3. Uma, MCA

  4. INTRODUCTION • Database – organized collection of data. Uma, MCA

  5. Database Management system – software application – interaction – between user & DB. • Python program can interact as a user of an SQL database. • INTRODUCTION OF SQLITE Database : • Simple relational database system Why we use SQLITE Database: - self-contained (no need for any server) - serverless(can run in client system itself) - zero-configuration(eg. Microsoft SQL server needs lot of configuration where sqlite no configuration is needed, directly can be used) - very small and light weight(nearly 400kb) (like mysql server all works can be done here) - written in C.(all the android application uses this DB, eg. Whatsapp, skype, vchat, telegram) Uma, MCA

  6. Instead of using separate database server program (MYSQL or Oracle), SQLITE is designed to be embedded in applications. • To use SQLite, • Step 1: import sqlite3 • Step 2 : connect() – to create connection to DB file • Step 3: cursor - major role working with python, all commands will be executed using cursor object only. • To create table in DB, • Eg: sql_command=“SQL statement” Uma, MCA

  7. Creating a Database using SQLite How a connection to be made to a database through python sqlite3 : import sqlite3 connection = sqlite3.connect ("Academy.db") cursor = connection.cursor() In the above eg, Academy – a databse name with Academy. It’s similar to sql command “create database Academy”. Uma, MCA

  8. Uma, MCA

  9. Creating a Table • The SQL Command for creating a table "Student" in the database "Academy" looks like as follows : • CREATE TABLE Student ( RollnoINTEGER, SnameVARCHAR(20), GradeCHAR(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. • 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. 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);""" Uma, MCA

  10. Uma, MCA

  11. Thank you • Prepared by, • M.Uma, M.C.A. • Teacher, Nagercoil Uma, MCA

More Related