Relational and non-relational databases

From Gridkaschool
Jump to: navigation, search
import json
import psycopg2
import web

urls = (
    '/', 'Everything',
    '/new', 'NewItem',
    '/item/(.+)/(.+)', 'ItemProperty',
    '/item/(.+)', 'Item',
    )



class Everything:
    def GET(self):  # curl http://0.0.0.0:8080/
        return json.dumps({"return" : "all the items from the database"})



class NewItem:
    def POST(self):  # curl -X POST -d '{"key1": "value1", "key2": "value2"}' http://0.0.0.0:8080/new
        d = json.loads(web.data())
        return json.dumps(d)



class Item:
    def GET(self, item):  # curl http://0.0.0.0:8080/item/item_name
        return json.dumps({"found" : "%s"%(item)})

    def PUT(self, item):  # curl -X PUT http://0.0.0.0:8080/item/item_name
        return json.dumps({"created" : item})

    def DELETE(self, item):  # curl -X DELETE http://0.0.0.0:8080/item/item_name
        return json.dumps({"deleted" : item})



class ItemProperty:
    def GET(self, item, prop):  # curl http://0.0.0.0:8080/item/item_name/property_name
        return json.dumps({"%s-%s"%(item, prop) : "the actual value of the property"})

    def PUT(self, item, prop):  # curl -X PUT http://0.0.0.0:8080/item/item_name/property_name=property_value
        p, v = prop.split('=')
        return json.dumps({"inserted": "%s,%s,%s"%(item,p,v)})

    def DELETE(self, item, prop):  # curl -X DELETE http://0.0.0.0:8080/item/item_name/property_name
        return json.dumps({"deleted": "%s-%s"%(item, prop)})



if __name__ == "__main__":
    web.config.debug = False
    app = web.application(urls, globals())
    app.run()


import datetime
import random
import uuid
import time
import pymongo
import bson

# connect to a mongodb server

m_con = pymongo.connection.Connection(host='localhost')

# connect to a collection in a database

db = m_con['my_database']
coll = db['my_collection']

# drop existing values
coll.drop()

tmp=[{'name': 'alice',
      'age': 23,
      'pets': [{'name': 'fido', 'age': 8},
               {'name': 'chuck', 'age': 1}]},
     {'name': 'bob',
      'age': 22},
     {'name': 'charlie',
      'age': 21},
     {'name': 'dave',
      'pets': [{'name': 'snuffles', 'age': 12},
               {'name': 'eve', 'age': 3}]},
     {'name': 'eve',
      'age': 25}]

coll.insert(tmp)

for d in coll.find():
    print d

# drop previous results
db['results'].drop()






# map everything

mapper = bson.Code("function () { emit(this.name, this.pets); }")
reducer = bson.Code("function (key, values) {}")

coll.map_reduce(mapper, reducer, 'results')


# print mapred results
print 'mapred 1'
for d in db['results'].find():
    print d






tmp=[{'url': 'cern.ch', 'timestamp': datetime.datetime.now()},
     {'url': 'cern.ch', 'timestamp': datetime.datetime.now()},
     {'url': 'kit.edu', 'timestamp': datetime.datetime.now()},
     {'url': 'kit.edu', 'timestamp': datetime.datetime.now()},
     {'url': 'kit.edu', 'timestamp': datetime.datetime.now()}]

coll.drop()
coll.insert(tmp)
db['results'].drop()



mapper = bson.Code("function () { emit(this.url, 1); }")
reducer = bson.Code("function (key, values) {var res = 0; values.forEach(function(v){res += 1}); return {'count': res};}")

coll.map_reduce(mapper, reducer, 'results')


# print mapred results
print 'mapred 2'
for d in db['results'].find():
    print d


import random                                                                                                                                                                                                                                
import uuid                                                                                                                                                                                                                                  
import time                                                                                                                                                                                                                                  
import pymongo                                                                                                                                                                                                                               
                                                                                                                                                                                                                                             
# connect to a mongodb server                                                                                                                                                                                                                
                                                                                                                                                                                                                                             
m_con = pymongo.connection.Connection(host='localhost')                                                                                                                                                                                      
                                                                                                                                                                                                                                             
# connect to a collection in a database                                                                                                                                                                                                      
                                                                                                                                                                                                                                             
db = m_con['my_database']                                                                                                                                                                                                                    
coll = db['my_collection']                                                                                                                                                                                                                   
                                                                                                                                                                                                                                             
# drop the collection                                                                                                                                                                                                                        
coll.drop()                                                                                                                                                                                                                                  
                                                                                                                                                                                                                                             
# insert                                                                                                                                                                                                                                     
time_start = time.time()
for i in xrange(10000):
    coll.insert({'id': str(uuid.uuid4()),
                 'value': random.randint(0, 1000)})
print time.time()-time_start


# straightforward
print 'count'
print coll.count()

print 'where value = 0'
for d in coll.find({'value': 0}):
    print d

print 'where value > 999'
for d in coll.find({'value': {'$gt': 999}}):
    print d

# create index
time_start = time.time()
coll.ensure_index([('value', pymongo.ASCENDING)], background=False)
print time.time()-time_start


# show query plan
print coll.find().explain()

print coll.find({'value': 100}).explain()

m_con.close()
drop table owns;
drop table product;
drop table pet;
drop table human;

create table human (name varchar(255) primary key, age integer);

create table pet (name varchar(255) primary key, age integer, owner varchar(255) references human(name));

insert into human values ('alice', 23);
insert into human values ('bob', 22);
insert into human values ('charlie', 21);
insert into human values ('dave', 24);
insert into human values ('eve', 25);

insert into pet values ('fido', 8, 'alice');
insert into pet values ('chuck', 1, 'alice');
insert into pet values ('snuffles', 12, 'dave');
insert into pet values ('truffles', 3, 'eve');

select h.name, p.name, p.age from human h, pet p where h.name = p.owner;




create table product(name varchar(255) primary key, cost integer);
insert into product values ('toothbrush', 12);
insert into product values ('towel', 22);
insert into product values ('magazine', 45);


create table owns(human varchar(255) references human(name), product varchar(255) references product(name), primary key (human, product));

insert into owns values ('alice', 'toothbrush');
insert into owns values ('alice', 'towel');
insert into owns values ('bob', 'toothbrush');
insert into owns values ('charlie', 'towel');
insert into owns values ('dave', 'towel');
insert into owns values ('dave', 'magazine');

select h.name, p.name, p.cost from human h, product p, owns o where h.name=o.human and p.name=o.product;

create table dl_test (id integer);

insert into dl_test values (1);
insert into dl_test values (2);



1:

begin transaction;

2:

begin transaction;

1:

update dl_test set id=100 where id=1;

2:

update dl_test set id=200 where id=2;

1:

update dl_test set id=2000 where id=2;

2:

update dl_test set id=1000 where id=1;

1:

commit;

2:

commit;


import sys
import random
import uuid
import time

import psycopg2

NR_OF_ROWS = 1000000

# connect to the database and set transaction level to autocommit
p_con = psycopg2.connect('host=localhost user=student password=student dbname=student')
p_con.set_isolation_level(0)  # 0 (AUTOCOMMIT), 1 (READ COMMIT), 3 (SERIALIZE)

# get cursor
p_cur = p_con.cursor()

# drop old tables if they exist, ignore otherwise
try: p_cur.execute("DROP TABLE large_table")
except: pass

# create table and enable transactions
p_cur.execute("CREATE TABLE large_table (l CHAR, s VARCHAR(255), n INTEGER)")
p_con.set_isolation_level(1)

# insert random data
time_start = time.time()
for i in xrange(NR_OF_ROWS):
    p_cur.execute("INSERT INTO large_table VALUES ('%s', '%s', %i)" % (chr(random.randrange(65, 91)),
                                                                       ''.join([chr(random.randrange(65, 91)) for i in xrange(1,16)]),
                                                                       random.randint(0,1000000)))
p_con.commit()
print 'insert', time.time()-time_start


# query plans

p_cur.execute("ANALYZE large_table")

p_cur.execute("EXPLAIN SELECT COUNT(*) FROM large_table")
for row in p_cur:
    print row

p_cur.execute("EXPLAIN SELECT * FROM large_table")
for row in p_cur:
    print row

p_cur.execute("EXPLAIN SELECT * FROM large_table WHERE l='A'")
for row in p_cur:
    print row

p_cur.execute("EXPLAIN SELECT * FROM large_table WHERE s='ASDFGH'")
for row in p_cur:
    print row

p_cur.execute("EXPLAIN SELECT * FROM large_table WHERE n=3")
for row in p_cur:
    print row

p_cur.execute("EXPLAIN SELECT * FROM large_table WHERE l='A' AND n=3")
for row in p_cur:
    print row

p_cur.execute("EXPLAIN SELECT * FROM large_table WHERE l='A' AND s='ASDF' AND n=3")
for row in p_cur:
    print row

time_start = time.time()
p_cur.execute("CREATE INDEX idxu_large_table_lsn ON large_table(l, s, n)")
print 'index', time.time()-time_start

p_cur.execute("EXPLAIN SELECT COUNT(*) FROM large_table")
for row in p_cur:
    print row

p_cur.execute("EXPLAIN SELECT * FROM large_table")
for row in p_cur:
    print row

p_cur.execute("EXPLAIN SELECT * FROM large_table WHERE l='A'")
for row in p_cur:
    print row

p_cur.execute("EXPLAIN SELECT * FROM large_table WHERE s='ASDFGH'")
for row in p_cur:
    print row

p_cur.execute("EXPLAIN SELECT * FROM large_table WHERE n=3")
for row in p_cur:
    print row

p_cur.execute("EXPLAIN SELECT * FROM large_table WHERE l='A' AND n=3")
for row in p_cur:
    print row

p_cur.execute("EXPLAIN SELECT * FROM large_table WHERE l='A' AND s='ASDF' AND n=3")
for row in p_cur:
    print row

    
# close everything

p_cur.close()
p_con.close()


import uuid
import time

import sqlite3
import MySQLdb
import MySQLdb.cursors
import psycopg2

NR_OF_ROWS = 100


# connect to the database and set transaction level to autocommit
s_con = sqlite3.connect(database='/tmp/student.db', isolation_level='DEFERRED')  # None, 'DEFERRED', 'IMMEDIATE', 'EXCLUSIVE'

m_con = MySQLdb.connect(host='localhost', user='student', passwd='student', db='student', cursorclass = MySQLdb.cursors.SSCursor)
m_con.autocommit(False)  # True, False

p_con = psycopg2.connect('host=localhost user=student password=student dbname=student')
p_con.set_isolation_level(0)  # 0 (AUTOCOMMIT), 1 (READ COMMIT), 3 (SERIALIZE)


# get cursor
s_cur = s_con.cursor()
m_cur = m_con.cursor()
p_cur = p_con.cursor()


# drop old tables if they exist, ignore otherwise
try: s_cur.execute("DROP TABLE large_table")
except: pass
try: m_cur.execute("DROP TABLE large_table")
except: pass
try: p_cur.execute("DROP TABLE large_table")
except: pass


# create table
s_cur.execute("CREATE TABLE large_table (id VARCHAR(255) PRIMARY KEY)")
m_cur.execute("CREATE TABLE large_table (id VARCHAR(255) PRIMARY KEY) Engine=InnoDB")
p_cur.execute("CREATE TABLE large_table (id VARCHAR(255) PRIMARY KEY)")
p_con.set_isolation_level(1)

# insert lots of rows and measure
print 'sqlite:',
time_start = time.time()
for i in xrange(NR_OF_ROWS):
    s_cur.execute("INSERT INTO large_table(id) VALUES (?)", (str(uuid.uuid4()),))
s_con.commit()
print time.time()-time_start

print 'mysql:',
time_start = time.time()
for i in xrange(NR_OF_ROWS):
    m_cur.execute("INSERT INTO large_table(id) VALUES (%s)", (str(uuid.uuid4()),))
m_con.commit()
print time.time()-time_start

print 'postgresql:',
time_start = time.time()
for i in xrange(NR_OF_ROWS):
    p_cur.execute("INSERT INTO large_table(id) VALUES (%s)", (str(uuid.uuid4()),))
p_con.commit()
print time.time()-time_start


# drop table
s_cur.execute("DROP TABLE large_table")
m_cur.execute("DROP TABLE large_table")
p_cur.execute("DROP TABLE large_table")


# close everything

s_cur.close()
m_cur.close()
p_cur.close()

s_con.close()
m_con.close()
p_con.close()
import sqlite3

import MySQLdb
import MySQLdb.cursors

import psycopg2


# connect to the database and set transaction level to autocommit
s_con = sqlite3.connect(database='/tmp/student.db', isolation_level=None)  # None, 'DEFERRED', 'IMMEDIATE', 'EXCLUSIVE'

m_con = MySQLdb.connect(host='localhost', user='student', passwd='student', db='student', cursorclass = MySQLdb.cursors.SSCursor)
m_con.autocommit(True)  # True, False

p_con = psycopg2.connect('host=localhost user=student password=student dbname=student')
p_con.set_isolation_level(0)  # 0 (AUTOCOMMIT), 1 (READ COMMIT), 3 (SERIALIZE)


# get cursor
s_cur = s_con.cursor()
m_cur = m_con.cursor()
p_cur = p_con.cursor()


# drop old tables if they exist, ignore otherwise
try: s_cur.execute("DROP TABLE test_table")
except: pass
try: m_cur.execute("DROP TABLE test_table")
except: pass
try: p_cur.execute("DROP TABLE test_table")
except: pass

    
# create table
s_cur.execute("CREATE TABLE test_table (id INTEGER PRIMARY KEY, value VARCHAR(255))")
m_cur.execute("CREATE TABLE test_table (id INTEGER PRIMARY KEY, value VARCHAR(255)) Engine=InnoDB")                                                                                                                                          
p_cur.execute("CREATE TABLE test_table (id INTEGER PRIMARY KEY, value VARCHAR(255))")                                                                                                                                                        
                                                                                                                                                                                                                                             
                                                                                                                                                                                                                                             
# insert data                                                                                                                                                                                                                                
s_cur.execute("INSERT INTO test_table(id, value) VALUES (0, 'my first value')")                                                                                                                                                              
s_cur.execute("INSERT INTO test_table(id, value) VALUES (1, 'my second value')")                                                                                                                                                             
s_cur.execute("INSERT INTO test_table(id, value) VALUES (2, 'my second value')")                                                                                                                                                             
                                                                                                                                                                                                                                             
m_cur.execute("INSERT INTO test_table(id, value) VALUES (0, 'my first value')")                                                                                                                                                              
m_cur.execute("INSERT INTO test_table(id, value) VALUES (1, 'my second value')")                                                                                                                                                             
m_cur.execute("INSERT INTO test_table(id, value) VALUES (2, 'my second value')")                                                                                                                                                             
                                                                                                                                                                                                                                             
p_cur.execute("INSERT INTO test_table(id, value) VALUES (0, 'my first value')")                                                                                                                                                              
p_cur.execute("INSERT INTO test_table(id, value) VALUES (1, 'my second value')")                                                                                                                                                             
p_cur.execute("INSERT INTO test_table(id, value) VALUES (2, 'my second value')")                                                                                                                                                             
                                                                                                                                                                                                                                             
                                                                                                                                                                                                                                             
# selecting data (everything)                                                                                                                                                                                                                
print 'sqlite'                                                                                                                                                                                                                               
s_cur.execute("SELECT id, value FROM test_table")                                                                                                                                                                                            
print s_cur.fetchall()                                                                                                                                                                                                                       
                                                                                                                                                                                                                                             
print 'mysql'                                                                                                                                                                                                                                
m_cur.execute("SELECT id, value FROM test_table")                                                                                                                                                                                            
print m_cur.fetchall()                                                                                                                                                                                                                       
                                                                                                                                                                                                                                             
print 'postgresql'
p_cur.execute("SELECT id, value FROM test_table")
print p_cur.fetchall()


# selecting data (iteratively)
print 'sqlite'
s_cur.execute("SELECT id, value FROM test_table")
for row in s_cur:
    print row

print 'mysql'
m_cur.execute("SELECT id, value FROM test_table")
for row in m_cur:
    print row

print 'postgresql'
p_cur.execute("SELECT id, value FROM test_table")
for row in p_cur:
    print row

# close everything

s_cur.close()
m_cur.close()
p_cur.close()

s_con.close()
m_con.close()
p_con.close()

import sqlite3                                                                                                                                                                                                                               
import MySQLdb                                                                                                                                                                                                                               
import psycopg2                                                                                                                                                                                                                              
                                                                                                                                                                                                                                             
                                                                                                                                                                                                                                             
s_con = sqlite3.connect(database='/home/username/student.db')                                                                                                                                                                                          
                                                                                                                                                                                                                                             
m_con = MySQLdb.connect(host='localhost', user='student', passwd='student', db='student')                                                                                                                                                  
                                                                                                                                                                                                                                             
p_con = psycopg2.connect('host=localhost user=student password=student dbname=student')                                                                                                                                                      
                                                                                                                                                                                                                                             
s_con.close()                                                                                                                                                                                                                                
m_con.close()                                                                                                                                                                                                                                
p_con.close()                                                                                                                                                                  

Hosts:

ssh <username>@<host>.scc.kit.edu -p24

  • gks-148
  • gks-149
  • gks-150
  • gks-151
  • gks-152
  • gks-153
  • gks-154
  • gks-155
  • gks-156
  • gks-157
  • gks-158
  • gks-159
  • gks-160
  • gks-161
  • gks-162
  • gks-163
  • gks-164
  • gks-165
  • gks-166
  • gks-167
  • gks-168
  • gks-169
  • gks-170
  • gks-171
  • gks-172
  • gks-173
  • gks-174
Personal tools