Relational and non-relational databases
From Gridkaschool
Jump to navigationJump to search
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
