Issuing Select query in Python code

Lesson Details:

I: Introduction

Introduction: Python is a general-purpose programming language. It was created by Guido van Rossum in 1991. It is named after the BBC comedy series Monty Python's Flying Circus. Python is any other interpreted, interactive, object-oriented programming language(OOP) that supports creation of dynamic and complex programs like C++, Perl, etc.

Python has many powerful features to make your code cleaner and easier to read. Python was designed to be highly readable, even to people who are not professional programmers.

II: Body

A: Issuing select query in python code

Select Query in python code: Let's see how we can issue sql query using python code. The following code uses Python SQLite module to connect to database and perform select query.

import sqlite3 con = sqlite3. connect ( 'bookstore.db' ) cur = con. cursor ( ) cur. execute ( "select book_name from book" ) result = cur. fetchall ( ) for row in result: print row [ 0 ]

In above python code, command 'sqlite3' is used to create connection with bookstore database. Once database connection is established, we can use command 'cur.execute("select book_name from book")' to execute select query on database. Command 'cur.fetchall()' is used to fetch all rows from the database as a list of tuples. The first element of tuple is a string containing the column name and remaining elements are values of corresponding columns. In above example, the output would be all books names stored in database as a list of tuples.

B: How to insert data into database using python code?

Let's see what steps are involved in inserting data into database using python code. Below python code creates a new table called 'author_details' and inserts some sample data into it. A dictionary is used as a parameter value to describe the structure of table and the values to insert into this table. Values inserted into table are retrieved back as a list of tuples which contains name of the column and actual values inserted into the table. For example, if we want to insert a new author called 'George Orwell', we will call a function called insert_data as shown below:

import sqlite3 con = sqlite3. connect ( 'bookstore.db' ) cur = con. cursor ( ) cur. execute ( "create table author_details(name varchar(20), surname varchar(20))" ) cur. executemany ( "insert into author_details values('George Orwell','Orwell')" , [ ( 'George Orwell' , 'Orwell' ) ] ) cur. execute ( "select * from author_details" ) rows = cur. fetchall ( ) for row in rows: print row [ 0 ] , row [ 1 ]

The program given below implements this functionality:

#!/usr/bin/python import sqlite3 con = sqlite3. connect ( "bookstore.db" ) cur = con. cursor ( ) new_table = { "name" : "author_details" , # Name of the table "columns" : [ # Columns and their types { "name" : "name" , "type" : "VARCHAR" } , { "name" : "surname" , "type" : "VARCHAR" } ] # This dictionary describes how structure of table } # Data to insert into the table defined above are inserted def insert_data ( ) : cur. execute ( """create table % s( % s);""" % ( new_table [ "name" ] , new_table [ "columns" ] ) ) cur. executemany ( """insert into % s values( % r);""" % ( new_table [ "name" ] , new_table [ "columns" ] ) , dflt = new_table [ "columns" ] ) dflt = new_table [ "columns" ] cur. execute ( """select * from % s""" % new_table [ "name" ] ) rows = cur. fetchall ( ) print rows # Execute the function to insert data into table insert_data ( ) con. commit ( ) con. close ( )

C: How to update data in database using python code?

Let's see how we can update data in database using python code. The function updatetable defined below accepts two parameters - name of table which needs to be updated and actual values to update corresponding columns in that table. We can execute the function updatetable as shown below:

def updatetable ( tbl , tup ) : tbl = tbl tup = tup tbl = tbl tup = tup cur . execute ( """update % s set % s=% r where % s=% r""" % ( tbl , tup [ 0 ] , tup [ 1 ] , tup [ 2 ] ) ) cur . execute ( """select * from % s""" % tbl ) rows = cur . fetchall ( ) print rows con . commit ( ) con . close ( )

We can use this function as shown below: