Difference between revisions of "Relational and non-relational databases"
From Gridkaschool
(13 intermediate revisions by the same user not shown) | |||
Line 1: | Line 1: | ||
<pre> |
<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> |
||
+ | import uuid |
||
+ | import time |
||
+ | |||
import sqlite3 |
import sqlite3 |
||
import MySQLdb |
import MySQLdb |
||
+ | import MySQLdb.cursors |
||
import psycopg2 |
import psycopg2 |
||
+ | NR_OF_ROWS = 100 |
||
− | s_con = sqlite3.connect(database='/tmp/student.db') |
||
+ | # connect to the database and set transaction level to autocommit |
||
− | m_con = MySQLdb.connect(host='localhost', user='student', passwd='student', db='student', ) |
||
+ | 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 = 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()</pre> |
||
+ | |||
+ | <pre> |
||
+ | 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() |
s_con.close() |
||
m_con.close() |
m_con.close() |
||
p_con.close() |
p_con.close() |
||
+ | |||
</pre> |
</pre> |
||
Line 31: | 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