Relational and non-relational databases

From Gridkaschool
Revision as of 14:18, 27 August 2013 by Mlassnig (talk | contribs)
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