Relational and non-relational databases
From Gridkaschool
Jump to navigationJump to search
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 (0, 'my second value')") s_cur.execute("INSERT INTO test_table(id, value) VALUES (0, '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 second value')") m_cur.execute("INSERT INTO test_table(id, value) VALUES (0, '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 second value')") p_cur.execute("INSERT INTO test_table(id, value) VALUES (0, '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 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 (0, 'my second value')")
s_cur.execute("INSERT INTO test_table(id, value) VALUES (0, '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 second value')")
m_cur.execute("INSERT INTO test_table(id, value) VALUES (0, '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 second value')")
p_cur.execute("INSERT INTO test_table(id, value) VALUES (0, '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()
CREATE TABLE test_table (id integer, name varchar(255));
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
