def testDatabase(self): self.assert_(os.path.exists, self.db) if isinstance(self.db_connect, dict): db = self.db.connect(**self.db_connect) else: db = self.db.connect(*self.db_connect) try: c = db.cursor() # Check that data got imported c.execute("SELECT count(*) FROM county") self.assertEqual(c.fetchone(), (22,)) c.execute("SELECT * FROM municipal WHERE municipal_id=1103") self.assertEqual(list(c.fetchall()), [(1103, "Stavanger", 11)]) c.execute("SELECT * FROM postal WHERE postal_no=4042") # FIXME: BOOL with mysql might not equal False self.assertEqual(c.fetchone(), (4042, "HAFRSFJORD", 1103, False)) # Check if wtf-8 worked fine. Note that the line # -*- coding: utf-8 -*- must be present at the top of # this file for the u"Østfold" thingie to work c.execute("SELECT county_name FROM county WHERE county_id=1") a = c.fetchone()[0] if not isinstance(a, unicode): a = a.decode("utf8") self.assertEqual(a, u"Østfold") finally: db.rollback()
USER = "******" PASSWORD = "******" DB = "sensor_data" sql_query = """INSERT INTO sensor_reading_of_infocity(ir, vacancy, total_water_used, total_water_wasted, time) VALUES ('%s', '%s', '%s', '%s', '%s') % \ (ir_db, vacancy_db, total_water_used_db, total_water_wasted_db, time_db ))""" try: connection = db.Connection(host=HOST, port=PORT, user=USER, passwd=PASSWORD, db=sensor_data) dbhandler = connection.cursor() dbhandler.execute(sql_query) db.commit() except: db.rollback() print "Error while inserting data into database" finally: connection.close() ################################# DATABASE Work END ########################################### except KeyboardInterrupt: print('\nCaught keyboard interrupt!, System is existing ,bye') except: print "System Failed to work"
def prepareTables(self): # First connect raw if isinstance(self.db_connect, dict): db = self.db.connect(**self.db_connect) else: db = self.db.connect(*self.db_connect) if self.db_mod == "postgresql": db.autocommit(1) try: c = db.cursor() if "sqlite" in str(db).lower(): # Ignore sync-checks for faster import c.execute("pragma synchronous = off") # Find our root by inspecting our own module import testforgetsql2 root = os.path.dirname(testforgetsql2.__file__) file = os.path.join(root, "test-data.sql") sql = codecs.open(file, encoding="utf8").read() # DROP TABLE if self.db_mod == "mysql": for table in ("county", "municipal", "postal", "insertion", "shop", "changed"): c.execute("DROP TABLE IF EXISTS %s" % table) elif self.db_mod == "postgresql": c.execute("""SELECT tablename FROM pg_catalog.pg_tables WHERE schemaname=pg_catalog.current_schema()""") existing = c.fetchall() for table in ("county", "municipal", "postal", "insertion", "shop", "changed"): if (table,) in existing: c.execute("DROP TABLE %s" % table) elif self.db_mod == "sqlite": # No need to drop tables in sqlite, we blank out the db each # time pass else: raise "Unknown db", self.db_mod # CREATE TABLE // EXECUTE if self.db_mod == "sqlite": # We have to fake since sqlite does not support the # fancy "bool" type. sql = sql.replace("FALSE", "0") sql = sql.replace("TRUE", "1") c.executescript(sql) elif self.db_mod in ("mysql", "postgresql"): for statement in sql.split(";"): if not statement.strip(): continue # Skip empty lines c.execute(statement.encode("utf8")) # Create database specific table "insertion" if self.db_mod == "sqlite": # This one is seperate because of "AUTOINCREMENT" vs "AUTO_INCREMENT" c.execute(""" CREATE TABLE insertion ( insertion_id INTEGER PRIMARY KEY AUTOINCREMENT, value VARCHAR(15) )""") elif self.db_mod == "mysql": c.execute(""" CREATE TABLE insertion ( insertion_id INTEGER PRIMARY KEY AUTO_INCREMENT, value VARCHAR(15) )""") elif self.db_mod == "postgresql": c.execute(""" CREATE TABLE insertion ( insertion_id SERIAL PRIMARY KEY, value VARCHAR(15) )""") else: raise "Unknown db", self.db_mod db.commit() finally: db.rollback()