Example #1
0
 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()
Example #2
0
        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"


Example #3
0
    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()