SQL programs

 Updating a record,deleting a record ,group by city ,count of students, average of marks,order in                   descending order 



First create a table to insert the values :

import sqlite3

con = sqlite3.connect('st2.db')

cursor = con.cursor()
sq = '''CREATE TABLE students(
               id INTEGER PRIMARY KEY,
               name TEXT ,
               marks INTEGER,
               city TEXT)'''

cursor.execute(sq)

print('table is created successfully')

con.commit()
con.close()


insert the values in the table:

import sqlite3

def muldata(dl):

    con = sqlite3.connect('st2.db')
    cursor = con.cursor()


    iq = '''INSERT INTO students
            VALUES(?,?,?,?) '''

    cursor.executemany(iq, dl)

    con.commit()
    print("no of records inserted: ",cursor.rowcount)
    con.commit()
    con.close()

dl = [(1,'raj',92,'banglore'),(2,'raju',91,'chennai'),(3,'rajesh',93,'mumbai'),(4,'martha',94,'banglore'),(5,'ulrich',90,'chennai'),(6,'jonas',89,'mumbai'),(7,'anna',97,'banglore'),(8,'hanna',96,'chennai'),(9,'charlotte',87,'mumbai'),(10,'thomas',79,'banglore')]

muldata(dl)


print and see how the table looks initially:

import sqlite3
con = sqlite3.connect('st2.db')

cursor = con.cursor()

sqlit = '''SELECT * FROM students
            '''

cursor.execute(sqlit)

records = cursor.fetchall()
print(records)

for row in records:
    print('id:', row[0])
    print('name:',row[1])
    print('marks:',row[2])



con.commit()
con.close()

1) updating a record :

import sqlite3
con = sqlite3.connect('st2.db')

cursor = con.cursor()

sqlit = '''UPDATE students
            SET name = 'mani'
            WHERE id = 1
            '''

cursor.execute(sqlit)

records = cursor.fetchall()
print(records)

for row in records:
    print('id:', row[0])
    print('name:',row[1])
    print('marks:',row[2])



con.commit()
con.close()

2) deleting a record:

import sqlite3
con = sqlite3.connect('st2.db')

cursor = con.cursor()

sqlit = '''DELETE FROM students 
            WHERE name = 'mani'
            '''

cursor.execute(sqlit)

records = cursor.fetchall()
print(records)

for row in records:
    print('id:', row[0])
    print('name:',row[1])
    print('marks:',row[2])



con.commit()
con.close()

3) Group by city:

import sqlite3
con = sqlite3.connect('st2.db')

cursor = con.cursor()

sqlit = '''SELECT city
FROM students
GROUP BY city;
            '''

cursor.execute(sqlit)

records = cursor.fetchall()
print(records)

'''for row in records:
    print('id:', row[0])
    print('name:',row[1])
    print('marks:',row[2])
'''


con.commit()
con.close()

 

4) marks in descending order:

import sqlite3
con = sqlite3.connect('st2.db')

cursor = con.cursor()

sqlit = '''SELECT marks
FROM students
ORDER BY marks DESC;
            '''

cursor.execute(sqlit)

records = cursor.fetchall()
print(records)

'''for row in records:
    print('id:', row[0])
    print('name:',row[1])
    print('marks:',row[2])
'''


con.commit()
con.close()


5) Average of marks :

import sqlite3
con = sqlite3.connect('st2.db')

cursor = con.cursor()

sqlit = '''SELECT AVG(marks)
FROM students

            '''

cursor.execute(sqlit)

records = cursor.fetchall()
print(records)

'''for row in records:
    print('id:', row[0])
    print('name:',row[1])
    print('marks:',row[2])
'''


con.commit()
con.close()

6)count of students:

import sqlite3
con = sqlite3.connect('st2.db')

cursor = con.cursor()

sqlit = '''SELECT count(id)
FROM students
            '''

cursor.execute(sqlit)

records = cursor.fetchall()
print(records)

'''for row in records:
    print('id:', row[0])
    print('name:',row[1])
    print('marks:',row[2])
'''


con.commit()
con.close()







Comments

Post a Comment

Popular posts from this blog

Queue in Java

Image
  Queue Interface In Java :   The Queue interface present in the java.util package and extends the Collection interface is used to hold the elements about to be processed in FIFO(First In First Out) order.  It is an ordered list of objects with its use limited to insert elements at the end of the list and deleting elements from the start of the list, (i.e.), it follows the FIFO or the First-In-First-Out principle.       queue needs a concrete class for the declaration and the most common classes are the PriorityQueue and LinkedList in Java.   Queue is an interface, objects cannot be created of the type queue. We always need a class which extends this list in order to create an object   Example : import java.util.LinkedList ; import java.util.Queue ; public class q { public static void main (String[] args) { Queue<Integer> qu = new LinkedList<>() ; for ( int i = 0 ; i < 5 ; i++) ...
Read more

Using lists , tuples, sets, dictionaries

  DAY 2 :   lists , tuples, sets, dictionaries lists: Lists are one of 4 built-in data types in Python used to store collections of data, the other 3 are tuple,set and dictionary, all with different qualities and usage. Lists are used to store multiple items in a single variable. Lists are created using square brackets     example: my_list = ["apple","banana","orange"]                         list2 = [5,7,9,6,3]   methods in list : append() Adds an element at the end of the list clear() Removes all the elements from the list copy() Returns a copy of the list count() Returns the number of elements with the specified value extend() Add the elements of a list (or any iterable), to the end of the current list index() Returns the index of the first element with the specified value insert() Adds an element at the specified position pop() Removes the element at the ...
Read more

Blog on Pytest

  Testing Python Applications with Pytest     Pytest is a testing framework and test runner for Python. In this guide, we will look at the most useful configuration and usage, including several pytest plugins and external libraries.   Pytest basics  Pytest will automatically scan our codebase for test modules, files following the file name convention test_*.py or *_test.py , and scan them for functions named test_*() . If we want to run tests defined as methods inside classes, we need to prefix the class names with Test . Pytest will also automatically pick up any tests written using unittest module.   Standard test functions and test runs from python_pytest . basics import add def test_add_can_add_numbers ( ) : # given num = 3 num2 = 45 # when result = add ( num , num2 ) # then assert result == 48 By default, fixtures are function-scoped, meaning that a fixture's lifetime is t...
Read more