Difference between revisions of "Relational and non-relational databases"

From Gridkaschool
 
(9 intermediate revisions by the same user not shown)
Line 1: Line 1:
  +
<pre>
  +
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()
  +
</pre>
  +
  +
  +
  +
<pre>
  +
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
  +
  +
</pre>
  +
  +
  +
  +
<pre>
  +
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()
  +
</pre>
  +
  +
<pre>
  +
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;
  +
  +
</pre>
  +
  +
<pre>
  +
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;
  +
  +
</pre>
  +
  +
  +
<pre>
  +
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()</pre>
  +
  +
 
<pre>
 
<pre>
 
import uuid
 
import uuid
Line 12: Line 410:
   
 
# connect to the database and set transaction level to autocommit
 
# 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'
+
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 = MySQLdb.connect(host='localhost', user='student', passwd='student', db='student', cursorclass = MySQLdb.cursors.SSCursor)
m_con.autocommit(True) # True, False
+
m_con.autocommit(False) # True, False
   
 
p_con = psycopg2.connect('host=localhost user=student password=student dbname=student')
 
p_con = psycopg2.connect('host=localhost user=student password=student dbname=student')
Line 26: Line 424:
 
p_cur = p_con.cursor()
 
p_cur = p_con.cursor()
   
  +
 
# drop old tables if they exist, ignore otherwise
+
# drop old tables if they exist, ignore otherwise
try: s_cur.execute("DROP TABLE large_table")
+
try: s_cur.execute("DROP TABLE large_table")
  +
except: pass
except: pass
 
 
try: m_cur.execute("DROP TABLE large_table")
 
try: m_cur.execute("DROP TABLE large_table")
 
except: pass
 
except: pass
Line 40: Line 438:
 
m_cur.execute("CREATE TABLE large_table (id VARCHAR(255) PRIMARY KEY) Engine=InnoDB")
 
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_cur.execute("CREATE TABLE large_table (id VARCHAR(255) PRIMARY KEY)")
  +
p_con.set_isolation_level(1)
 
   
 
# insert lots of rows and measure
 
# insert lots of rows and measure
Line 46: Line 444:
 
time_start = time.time()
 
time_start = time.time()
 
for i in xrange(NR_OF_ROWS):
 
for i in xrange(NR_OF_ROWS):
pass # YOUR CODE HERE, USE str(uuid.uuid4())
+
s_cur.execute("INSERT INTO large_table(id) VALUES (?)", (str(uuid.uuid4()),))
  +
s_con.commit()
 
print time.time()-time_start
 
print time.time()-time_start
   
Line 52: Line 451:
 
time_start = time.time()
 
time_start = time.time()
 
for i in xrange(NR_OF_ROWS):
 
for i in xrange(NR_OF_ROWS):
pass # YOUR CODE HERE, USE str(uuid.uuid4())
+
m_cur.execute("INSERT INTO large_table(id) VALUES (%s)", (str(uuid.uuid4()),))
  +
m_con.commit()
 
print time.time()-time_start
 
print time.time()-time_start
   
Line 58: Line 458:
 
time_start = time.time()
 
time_start = time.time()
 
for i in xrange(NR_OF_ROWS):
 
for i in xrange(NR_OF_ROWS):
pass # YOUR CODE HERE, USE str(uuid.uuid4())
+
p_cur.execute("INSERT INTO large_table(id) VALUES (%s)", (str(uuid.uuid4()),))
  +
p_con.commit()
 
print time.time()-time_start
 
print time.time()-time_start
   
Line 76: Line 477:
 
s_con.close()
 
s_con.close()
 
m_con.close()
 
m_con.close()
p_con.close()
+
p_con.close()</pre>
 
</pre>
 
   
 
<pre>
 
<pre>
Line 122: Line 521:
 
# insert data
 
# 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 (0, 'my first value')")
s_cur.execute("INSERT INTO test_table(id, value) VALUES (0, 'my second 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 (0, '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 (0, 'my first value')")
m_cur.execute("INSERT INTO test_table(id, value) VALUES (0, 'my second 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 (0, '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 (0, 'my first value')")
p_cur.execute("INSERT INTO test_table(id, value) VALUES (0, 'my second 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 (0, 'my second value')")
+
p_cur.execute("INSERT INTO test_table(id, value) VALUES (2, 'my second value')")
 
 
 
 
Line 191: Line 590:
 
m_con.close()
 
m_con.close()
 
p_con.close() </pre>
 
p_con.close() </pre>
 
 
 
CREATE TABLE test_table (id integer, name varchar(255));
 
 
   
 
Hosts:
 
Hosts:

Latest revision as of 17:00, 27 August 2013

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