better sqlite3 sample file

bison-- · bison-- · commit 292765d35a20 · 2015-03-15T18:16:28.000+01:00
diff --git a/.gitignore b/.gitignore
@@ -33,3 +33,7 @@ nosetests.xml
 .mr.developer.cfg
 .project
 .pydevproject
+
+
+beginnerscorner/tests/sqlite/test1.db
+.idea
diff --git a/beginnerscorner/tests/sqlite/test1.py b/beginnerscorner/tests/sqlite/test1.py
@@ -13,32 +13,55 @@
 cursor = connection.cursor()
 
 # erste Tabelle anlegen
-cursor.execute("""CREATE TABLE user (id INTEGER, name TEXT, vorname TEXT, pass TEXT)""")
-cursor.execute("""CREATE TABLE chanel (id INTEGER, name TEXT, pass TEXT, typ INTEGER)""")
+cursor.execute("CREATE TABLE IF NOT EXISTS user (id INTEGER PRIMARY KEY AUTOINCREMENT, name TEXT, vorname TEXT, pass TEXT, personalNummer INTEGER)")
+cursor.execute("CREATE TABLE IF NOT EXISTS chanel (id INTEGER PRIMARY KEY AUTOINCREMENT, name TEXT, pass TEXT, typ INTEGER)")
 
-# Daten in die Tabelle fuellen
-cursor.execute("""INSERT INTO user VALUES ( 1, 'oerb', 'the Real', 'hallo')""")
-cursor.execute("""INSERT INTO user VALUES ( 2, 'bison', 'The Warhammer', 'hallo1')""")
-connection.commit() # solange commit nicht ausgef&uuml;hrt, sind daten eventuell noch nicht geschrieben
+# Daten in die Tabelle fuellen (is aber kacke so)
+cursor.execute("INSERT INTO user VALUES (NULL, 'oerb', 'the Real', 'hallo', 1337);")
+cursor.execute("INSERT INTO user VALUES (NULL, 'bison', 'The Warhammer', 'hallo1', 23);")
+connection.commit()  # solange commit nicht ausgefuehrt, sind daten eventuell noch nicht geschrieben
 
 # Sichere Datenuebergabe mit einem Dictionary 
-werte = { "id" : "3", "name" : "00", "vorname" : "M", "pass" : "hallo3" }
-sql = "INSERT INTO user VALUES (:id, :name, :vorname, :pass)"
+werte = { "id" : None, "name" : "00", "vorname" : "M", "pass" : "hallo3", "personalNummer": 01 }
+sql = "INSERT INTO user VALUES (:id, :name, :vorname, :pass, :personalNummer)"
+cursor.execute(sql, werte)
+
+connection.commit()
+
+
+werte = {"id": None, "name" : "01", "vorname" : "M", "pass" : "hallo3", "personalNummer": 02 }
+sql = "INSERT INTO user VALUES (:id, :name, :vorname, :pass, :personalNummer)"
 cursor.execute(sql, werte)
 
 connection.commit()
 
 # alternativ geht auch folgendes
-for row in ((4, "superhero", "Hero", "karabanga"),
-            (4, "Nemo", "Captain", "Nautilus")):
-    cursor.execute("INSERT INTO user VALUES(?,?,?,?)",row)
+for row in ((None, "superhero", "Hero", "karabanga", 77),
+            (None, "Nemo", "Captain", "Nautilus", 88)):
+    cursor.execute("INSERT INTO user VALUES(?,?,?,?,?)", row)
 
 connection.commit()
 
-# ne kleine selectabfrage
+# ne kleine selectabfrage (in kacke)
+cursor.execute("SELECT vorname, name FROM user WHERE personalNummer>2")
+dataRows = cursor.fetchall()
+
+for data in dataRows:
+    print(data)
+
+print dataRows[0]
+
+print '--------------------'
+
+# ne kleine selectabfrage (in "sicher")
+cursor.execute("SELECT vorname, name FROM user WHERE personalNummer>:personalNummer",
+        {"personalNummer": 2})
+dataRows = cursor.fetchall()
+
+for data in dataRows:
+    print(data)
+
+print dataRows[0]
 
-cursor.execute("SELECT vorname, name FROM user WHERE id=1")
-data = cursor.fetchall()
 
-print(data[0])
-connection.close()
+connection.close()
