Database - Application - python

Ahmad Yoosofan

University of Kashan

Simple Application(I)

 1 import sqlite3
 2 conn = sqlite3.connect('sp.sqlite')
 3 c = conn.cursor()
 4 r1 = c.execute('select * from "s";')
 5 print(r1)
 6 for m1 in r1:
 7   print(m1)
 8 c.close()
 9 conn.close()

Cursor(I)

 1 import sqlite3
 2 conn = sqlite3.connect('sp.sqlite')
 3 c1 = conn.cursor()
 4 c2 = conn.cursor()
 5 
 6 r1 = c1.execute('select * from s;')
 7 r2 = c2.execute('select * from p;')
 8 
 9 print('S')
10 for m1 in r1:
11   print(m1)
12 
13 print('P')
14 for m1 in r2:
15   print(m1)
16 
17 c1.close()
18 c2.close()
19 conn.close()

Cursor(II)

 1 import sqlite3
 2 conn = sqlite3.connect('sp.sqlite')
 3 c = conn.cursor()
 4 
 5 r1 = c.execute('select * from s;')
 6 
 7 print('S')
 8 for m1 in r1:
 9   print(m1)
10 
11 r2 = c.execute('select * from p;')
12 
13 print('P')
14 for m1 in r2:
15   print(m1)
16 
17 c.close()
18 conn.close()

Cursor(III)

 1 import sqlite3
 2 conn = sqlite3.connect('sp.sqlite')
 3 c = conn.cursor()
 4 r1 = c.execute('select * from s;')
 5 r2 = c.execute('select * from p;')
 6 print('r1: ', type(r1))
 7 print('r2:',  type(r2))
 8 r1 = ( ('p4', 'cog', 'green', 13),
 9         ('p8', 'bolt', 'red', 32)
10      )
11 r2 = ( ('p15', 'screw', 'blue', 76),
12        ( 'p45', 'Nut', 'Red', 27)
13      )
14 print('r1: ', type(r1))
15 print('r2:',  type(r2))
16 print('S')
17 r1 = c.execute('select * from s;')
18 r2 = c.execute('select * from p;')
19 for m1 in r1:
20   print(m1)
21 print('P')
22 for m1 in r2:
23   print(m1)
24 c.close()
25 conn.close()

 1 python3 sp260.py
 2 
 3 r1:  <class 'sqlite3.Cursor'>
 4 r2: <class 'sqlite3.Cursor'>
 5 r1:  <class 'tuple'>
 6 r2: <class 'tuple'>
 7 S
 8 ('P1', 'Nut', 'Red', 12, 'London')
 9 ('P2', 'Bolt', 'Green', 17, 'Paris')
10 ('P3', 'Screw', 'Blue', 17, 'Oslo')
11 ('P4', 'Screw', 'Red', 14, 'London')
12 ('P5', 'Cam', 'Blue', 12, 'Paris')
13 ('P6', 'Cog', 'Red', 19, 'London')
14 P

Traverse Fields

 1 import sqlite3
 2 conn = sqlite3.connect('sp.sqlite')
 3 c1 = conn.cursor()
 4 c2 = conn.cursor()
 5 
 6 r1 = c1.execute('select * from s;')
 7 r2 = c2.execute('select * from p;')
 8 
 9 print('S')
10 for m1 in r1:
11   for k1 in m1:
12     print(k1, end=" ;  ")
13   print();
14 
15 print('P')
16 for m1 in r2:
17   print(m1)
18 
19 c1.close()
20 c2.close()
21 
22 conn.close()

 1 python3 sp280.py
 2 
 3 S
 4 S1 ;  Smith ;  20 ;  London ;
 5 S2 ;  Jones ;  10 ;  Paris ;
 6 S3 ;  Blake ;  30 ;  Paris ;
 7 S4 ;  Clark ;  20 ;  London ;
 8 S5 ;  Adams ;  30 ;  Athens ;
 9 S6 ;  Ali ;  40 ;  کاشان ;
10 P
11 ('P1', 'Nut', 'Red', 12, 'London')
12 ('P2', 'Bolt', 'Green', 17, 'Paris')
13 ('P3', 'Screw', 'Blue', 17, 'Oslo')
14 ('P4', 'Screw', 'Red', 14, 'London')
15 ('P5', 'Cam', 'Blue', 12, 'Paris')
16 ('P6', 'Cog', 'Red', 19, 'London')
17

Fetch

 1 import sqlite3
 2 conn = sqlite3.connect('sp.sqlite')
 3 c1 = conn.cursor()
 4 c2 = conn.cursor()
 5 
 6 r1 = c1.execute('select * from s;')
 7 r2 = c2.execute('select * from p;')
 8 
 9 print('S')
10 for m1 in r1:
11   print(m1)
12 
13 print('P')
14 print(c2.fetchone())
15 print(c2.fetchone())
16 print(r2.fetchone())
17 
18 c1.close()
19 c2.close()
20 conn.close()

 1 python3 sp280.py
 2 
 3 S
 4 ('S1', 'Smith', 20, 'London')
 5 ('S2', 'Jones', 10, 'Paris')
 6 ('S3', 'Blake', 30, 'Paris')
 7 ('S4', 'Clark', 20, 'London')
 8 ('S5', 'Adams', 30, 'Athens')
 9 ('S6', 'Ali', 40, 'کاشان')
10 P
11 ('P1', 'Nut', 'Red', 12, 'London')
12 ('P2', 'Bolt', 'Green', 17, 'Paris')
13 ('P3', 'Screw', 'Blue', 17, 'Oslo')
14

Manipulate Tables(I)

 1 import sqlite3
 2 conn = sqlite3.connect('sp.sqlite')
 3 c = conn.cursor()
 4 c.execute('''
 5   insert into p(pn, pname, color, weight, city)
 6   values('p19','Nut'  ,'Red'  ,12.0,'London')
 7   ;
 8  ''')
 9 r1 = c.execute('select * from p;')
10 for m1 in r1:
11   print(m1)
12 print('before commit')
13 conn.commit()
14 print('after commit')
15 r1 = c.execute('select * from p;')
16 for m1 in r1:
17   print(m1)
18 c.close()
19 conn.close()

Manipulate Tables(II)

 1 import sqlite3
 2 conn = sqlite3.connect('sp.sqlite')
 3 c1 = conn.cursor()
 4 c2 = conn.cursor()
 5 c1.execute('''
 6   insert into p(pn, pname, color, weight, city)
 7   values('p20','Nut'  ,'Red'  ,12.0,'London')
 8   ;
 9  ''')
10 r1 = c2.execute('select * from p;')
11 for m1 in r1:
12   print(m1)
13 print('before commit')
14 conn.commit()
15 print('after commit')
16 r1 = c2.execute('select * from p;')
17 for m1 in r1:
18   print(m1)
19 c1.close()
20 c2.close()
21 conn.close()

Two Connection

 1 import sqlite3
 2 conn = sqlite3.connect('sp.sqlite')
 3 conn1 = sqlite3.connect('sp.sqlite')
 4 c = conn.cursor()
 5 c1 = conn1.cursor()
 6 c.execute('''
 7   insert into p(pn, pname, color, weight, city)
 8   values('p22','Nut'  ,'Red'  ,12.0,'London')
 9   ;
10  ''')
11 r1 = c1.execute('select * from p;')
12 for m1 in r1:
13   print(m1)
14 print('before commit')
15 conn.commit()
16 print('after commit')
17 r1 = c1.execute('select * from p;')
18 for m1 in r1:
19   print(m1)
20 c.close()
21 c1.close()
22 conn.close()

DELETE

 1 import sqlite3
 2 conn = sqlite3.connect('sp.sqlite')
 3 c = conn.cursor()
 4 c.execute('''delete from p where pn='p19';''')
 5 conn.commit()
 6 r1 = c1.execute('select * from p;')
 7 for m1 in r1:
 8   print(m1)
 9 c.close()
10 conn.close()

A program(I)

 1 def createTable(c):
 2   c.execute('''
 3     CREATE TABLE IF NOT EXISTS stocks (
 4     date1 char(10),
 5     trans char(15),
 6     symbol char(10),
 7     qty numeric(8,2),
 8     price numeric(8,2),
 9     primary key(date1)
10   );
11   ''')
12   c.execute('''
13     INSERT INTO stocks VALUES (
14       '2006-01-05'
15       ,'BUY', 'RHAT', 100, 35.14
16     );
17   ''')
18 
19 def test2(c):
20   t = ('RAT',)
21   c.execute('''
22     SELECT *
23     FROM stocks
24     WHERE symbol=?
25   ''', t)

A program(II)

26   print(c.fetchone())
27   purchases = [
28     ('2006-03-28', 'BUY', 'IBM', 1000, 45.00),
29     ('2006-04-05', 'BUY', 'MSFT', 1000, 72.00),
30     ('2006-04-06', 'SELL', 'IBM', 500, 53.00),
31   ]
32   c.executemany('INSERT INTO stocks VALUES (?,?,?,?,?)', purchases)
33 
34 def testDelete(c):
35   c.execute("DELETE FROM stocks WHERE symbol='IBM';")
36   c.execute("select * from stocks");
37   print("آخرین خروچی‌های");
38   print(c.fetchone())
39 
40 def testFor(c):
41   for row in c.execute('SELECT * FROM stocks ORDER BY price'):
42     print(row)
43 
44 path1 = 'example.db'
45 conn = sqlite3.connect(path1)
46 c = conn.cursor()
47 #createTable(c)
48 #test2(c)
49 testDelete(c)
50 #testFor(c)
51 c.close(); conn.commit() #os.system('rm example.db')
52 if os.path.exists(path1):
53   os.remove(path1)

 1 import psycopg
 2 conn = psycopg.connect(dbname="sp", user="ali",
 3         password="12", host = '127.0.0.1')
 4 cur = conn.cursor()
 5 cur.execute("select * from s;");
 6 for a in cur.fetchall():
 7   print(a)
 8 cur.close()
 9 conn.close()

 1 import psycopg
 2 conn = psycopg.connect(dbname="sp", user="ali",
 3          password="12", host = '127.0.0.1')
 4 cur = conn.cursor()
 5 cur.execute("select * from s;");
 6 for a in cur.fetchall():
 7   print('[ ', end = '')
 8   for m1 in a:
 9     if type(m1) is str:
10       print(m1.strip(), end=', ')
11     else:
12       print('not str:: ',m1, end=', ')
13   print(']')
14 cur.close()
15 conn.close()

 1 import psycopg
 2 conn = psycopg.connect(dbname="sp", user="ali",
 3          password="12",host='127.0.0.1')
 4 cur = conn.cursor()
 5 #cur.execute("select * from a1;");
 6 cur.execute("""create table a1(
 7                     mn int primary key,
 8                     st char(20)
 9                 );
10             """)
11 conn.commit();
12 cur.close()
13 conn.close()

 1 import psycopg
 2 conn = psycopg.connect(dbname="sp", user="ali",
 3          password="12",host='127.0.0.1')
 4 cur = conn.cursor()
 5 #cur.execute("select * from a1;");
 6 cur.execute("""CREATE TABLE IF NOT EXISTS "a1"(
 7                     "mn" INTEGER PRIMARY KEY,
 8                     "st" VARCHAR(20)
 9                 );
10             """)
11 conn.commit();
12 cur.close()
13 conn.close()

 1 import psycopg
 2 conn = psycopg.connect(dbname="sp", user="ali",
 3          password="12",host='127.0.0.1')
 4 cur = conn.cursor()
 5 cur.execute("""ALTER TABLE "a1" ADD "status" INTEGER;""");
 6 #input("Enter something: ")
 7 conn.commit();
 8 cur.execute("""SELECT * FROM "a1";""")
 9 cur.close()
10 conn.close()

 1 import psycopg
 2 conn = psycopg.connect(dbname="sp", user="ali",
 3         password="12",host='127.0.0.1')
 4 cur = conn.cursor()
 5 conn.commit();
 6 cur.execute("select * from a1;");
 7 for a in cur.fetchall():
 8   print(a)
 9 cur.close()
10 conn.close()

 1 import psycopg
 2 conn = psycopg.connect(dbname="sp", user="postgres",
 3           password="12",host='127.0.0.1')
 4 cur = conn.cursor()
 5 cur.execute("""SELECT * FROM "s";""");
 6 for a in cur.fetchall():
 7   print(a)
 8 cur.close()
 9 conn.commit()
10 conn.close()
11 
12 # psql --username=postgres --dbname=sp  --host=127.0.0.1  --password
13 # INSERT INTO "p"("pn", "pname", "color", "weight", "city") VALUES('p7','Bolt', 'Green', 12.1, 'Shiraz');
14 
15 # with psk as (select city from p natural join s)
16 # select * from psk;
17 # CREATE OR REPLACE VIEW "psk" ("city") AS SELECT DISTINCT "p"."city" FROM "s" JOIN "p" ON "s"."city" = "p"."city" ;
18 """
19 CREATE OR REPLACE VIEW "psk" ("city") AS
20     SELECT DISTINCT "p"."city"
21     FROM "s" JOIN "p" ON "s"."city" = "p"."city" ;
22 """

 1 import psycopg
 2 conn = psycopg.connect(dbname="sp", user="postgres",
 3           password="12",host='127.0.0.1')
 4 cur = conn.cursor()
 5 conn.commit();
 6 cur.execute("select * from a1;");
 7 for a in cur.fetchall():
 8   print(a)
 9 #cur.execute("insert into a1(sno,status) values('s4','alll');")
10 cur.execute("""INSERT INTO "a1"("mn", "st", "status") VALUES
11             (4, 'S4', 15);""")
12 #input('Enter Something:: ')
13 cur.execute("""UPDATE "a1" SET "status" = 40 WHERE "st"='S4';""")
14 cur.execute("select * from a1;");
15 for a in cur.fetchall():
16   print(a)
17 conn.commit()
18 
19 cur.close()
20 conn.close()

 1 #Error
 2 import psycopg
 3 conn = psycopg.connect(dbname="sp", user="ali", password="12",host='127.0.0.1')
 4 cur = conn.cursor()
 5 cur.execute("select * from a1;");
 6 for a in cur.fetchall():
 7   print(a)
 8 cur.execute("update a1 set status=10 where sn='s1';")
 9 cur.execute("update a1 set status=20 where sn='s2';")
10 #cur.execute("update a1 set status=10 where st='s1';")
11 #cur.execute("update a1 set status=20 where st='s2';")
12 conn.commit();
13 #cur.execute("select * from a1;")
14 for a in cur.fetchall():
15   print(a)
16 cur.close()
17 conn.close()

 1 import psycopg
 2 conn = psycopg.connect(dbname="sp", user="postgres", password="12",host='127.0.0.1')
 3 cur = conn.cursor()
 4 cur.execute("select * from s;");
 5 for a in cur.fetchall():
 6   print(a)
 7 print()
 8 n1=input('Enter Status for s1 :  ')
 9 si=input("Enter si : ");
10 stmt="update s set status="+str(n1)+" where sn='"+si+"';"
11 stmt="update s set status="+str(n1)+" where sn='"+"'S12'"+"';"
12 stmt="update s set status="+str(n1)+" where sn='"+"S12' or \'32\'='32"+"';"
13 
14 # update s set status=13 where sn='S12' or '32'='32';
15 
16 stmt="update s set status="+str(n1)+" where sn='"
17 stmt+="S12'; insert into p(pn, pname, weight, city) values('p31', 'ddd', 43, 'Kashan');"
18 stmt+="select * from p where pn='p4343"+"';"
19 
20 print(stmt)
21 cur.execute(stmt)
22 #cur.execute("update s set status=20 where sn='s3';")
23 conn.commit();
24 print();
25 cur.execute("select * from s;");
26 for a in cur.fetchall():
27   print(a)
28 cur.close()
29 conn.close()

 1 import psycopg
 2 conn = psycopg.connect(dbname="sp", user="postgres", password="12",host='127.0.0.1')
 3 cur = conn.cursor()
 4 cur.execute("select * from a1;");
 5 for a in cur.fetchall():
 6   print(a)
 7 cur.execute("update a1 set status=10 where sno='s1';")
 8 cur.execute("update a1 set status=20 where sno='s3';")
 9 conn.commit();
10 cur.execute("select * from a1;");
11 for a in cur.fetchall():
12   print(a)
13 cur.close()
14 conn.close()

 1 import psycopg
 2 conn = psycopg.connect(dbname="sp", user="postgres", password="12",host='127.0.0.1')
 3 cur = conn.cursor()
 4 s1=input('Enter a sn: ')
 5 sname1=input('Enter sname ')
 6 stmt1 ="insert into s(sn, sname, status, city) values('";
 7 stmt1+=s1+"','"+sname1
 8 stmt1+="',30,'Athens');"
 9 cur.execute(stmt1)
10 cur.execute("select * from s;");
11 for a in cur.fetchall():
12   print(a)
13 cur.close()
14 conn.commit()
15 conn.close()

 1 import psycopg
 2 conn = psycopg.connect(dbname="sp", user="postgres", password="12",host='127.0.0.1')
 3 cur = conn.cursor()
 4 s1=input('Enter a sn: ')
 5 sname1=input('Enter sname ')
 6 stmt1 ="insert into s(sn, sname, status, city) values('";
 7 stmt1+=s1+"','"+sname1
 8 stmt1+="',30,'Athens');"
 9 cur.execute(stmt1)
10 cur.execute("select * from s;");
11 for a in cur.fetchall():
12   print(a)
13   for l1 in a:
14     #print(l1,type(l1),end=' ');
15     if type(l1) != int and type(l1)!=float and l1!=None:
16       print(l1,len(l1),end=' ');
17   print()
18 cur.close()
19 conn.commit()
20 conn.close()

 1 import psycopg
 2 conn = psycopg.connect(dbname="sp", user="postgres", password="12",host='127.0.0.1')
 3 cur = conn.cursor()
 4 cur.execute("select * from s;");
 5 for a in cur.fetchall():
 6   print(a)
 7 print()
 8 n1=input('Enter Status for s1 :  ')
 9 si=input("Enter si : ");
10 stmt="update s set status="+str(n1)+" where sn='"+si+"';"
11 stmt="update s set status="+str(n1)+" where sn='"+"'S12'"+"';"
12 stmt="update s set status="+str(n1)+" where sn='"+"S12' or \'32\'='32"+"';"
13 
14 # update s set status=13 where sn='S12' or '32'='32';
15 
16 stmt="update s set status="+str(n1)+" where sn='"
17 stmt+="S12'; insert into p(pn, pname, weight, city) values('p31', 'ddd', 43, 'Kashan');"
18 stmt+="select * from p where pn='p4343"+"';"
19 
20 print(stmt)
21 cur.execute(stmt)
22 #cur.execute("update s set status=20 where sn='s3';")
23 conn.commit();
24 print();
25 cur.execute("select * from s;");
26 for a in cur.fetchall():
27   print(a)
28 cur.close()
29 conn.close()

 1 import psycopg
 2 conn = psycopg.connect(dbname="sp", user="postgres",
 3         password="12",host='127.0.0.1')
 4 cur = conn.cursor()
 5 cur.execute("select sn, avg(qty) from sp group by sn;");
 6 for a in cur.fetchall():
 7   print(a)
 8 cur.execute("select sn,qty from sp;")
 9 sum=0;
10 prev='s00'
11 for a in cur.fetchall():
12   if a[0]==prev:
13     sum+=a[1];
14   else:
15     if prev!='s00':
16       print(prev, sum)
17     sum=a[1]
18     prev=a[0]
19 cur.close()
20 conn.close()

 1 import psycopg
 2 #conn = psycopg.connect(dbname="sp", user="postgres", password="12",host='localhost')
 3 conn = psycopg2.connect(dbname="sp", user="postgres", password="12344321",host='127.0.0.1')
 4 cur = conn.cursor()
 5 #cur.execute("SELECT * from s")
 6 #print(cur.fetchone())
 7 #cur.execute(
 8 # "create view vs2 as select sno,pno,sname from s natural join sp;");
 9 #cur.execute(
10 # "select * from vs2");
11 #print(cur.fetchone());
12 #for a  in cur.fetchall():
13 #  print(a)
14 #cur.execute(
15 #  "insert into s values ('s6','ali',20,null);")
16 #conn.commit();
17 cur.execute("select * from s;");
18 for a in cur.fetchall():
19   for l1 in a:
20     #print(l1,type(l1),end=' ');
21     if type(l1) != int and type(l1)!=float and l1!=None:
22       print(l1,len(l1),end=' ');
23   print()
24 cur.execute('''
25 create table if not exists a2 (
26   sn varchar(10),
27   sname varchar(25),
28   primary key(sn)
29 );
30 ''')
31 conn.commit()
32 cur.execute(
33  "insert into a2 values('s1','ali');")
34 conn.commit()
35 cur.execute("select * from a1;");
36 for a in cur.fetchall():
37   for l1 in a:
38     #print(l1,type(l1),end=' ');
39     if type(l1) != int and type(l1)!=float and l1!=None:
40       print(l1,len(l1),end=' ');
41   print()
42 cur.close()
43 conn.close()

 1 #https://www.psycopg.org/docs/usage.html
 2 import psycopg
 3 conn = psycopg.connect(dbname="sp", user="postgres",
 4         password="12",host='127.0.0.1')
 5 cur = conn.cursor()
 6 sql = """
 7       CREATE TABLE test (
 8         id serial PRIMARY KEY,
 9         num integer,
10         data varchar);
11       """
12 cur.execute(sql)
13 sql = "INSERT INTO test (num, data) VALUES (%s, %s)"
14 cur.execute(sql, (100, "abc'def"))
15 
16 cur.execute("SELECT * FROM test;")
17 cur.fetchone()
18 conn.commit()
19 cur.close()
20 conn.close()