Example #1
1
    def __init__(self, host="", port=0, user="", passwd="", db="", dbConfig=""):
        try:
            self.sql = u""
            if host != "":
                self.host = host
                self.port = port
                self.user = user
                self.passwd = passwd
                self.db = db
                self.connect = MySQLdb.connect(host=host, port=port, user=user, passwd=passwd, db=db, charset="utf8")
            else:

                config = json.loads(
                    open(r"E:\xuweitao\WebSpider\taobao\DBconfigure.json" if dbConfig == "" else dbConfig).read()
                )
                self.host = config["host"]
                self.port = config["port"]
                self.user = config["user"]
                self.passwd = config["passwd"]
                self.db = db if db != "" else config["database"]
                self.connect = MySQLdb.connect(
                    host=config["host"],
                    port=config["port"],
                    user=config["user"],
                    passwd=config["passwd"],
                    db=db if db != "" else config["database"],
                    charset="utf8",
                )
            self.cursor = self.connect.cursor()
        except MySQLdb.Error, e:
            pass
Example #2
1
    def getConn(type_name):
        if DBFactory.my_kv.has_key(type_name):
            return DBFactory.my_kv[type_name]

        if type_name in ("finance", "adclick", "adupdate", "adpublish"):
            url_key = "hibernate.connection.%s.url" % type_name
            username_key = "hibernate.connection.%s.username" % type_name
            passsword_key = "hibernate.connection.%s.password" % type_name

            url = DBFactory.kv_dict.get(url_key, "")
            _user = DBFactory.kv_dict.get(username_key, "")
            _passwd = DBFactory.kv_dict.get(passsword_key, "")
            if url == "" or _user == "" or _passwd == "":
                sys.exit(1)

            reObj = re.search("jdbc:mysql://(\w+)/(.*?)\?", url)
            if not reObj:
                sys.exit(1)

            _host = reObj.group(1)
            _db = reObj.group(2)

            DBFactory.my_kv[type_name] = MySQLdb.connect(
                host=_host, user=_user, passwd=_passwd, db=_db, port=3306, charset="utf8"
            )

        elif type_name in ("logdb",):
            DBFactory.my_kv[type_name] = MySQLdb.connect(
                host="tb081", user="test", passwd="test", db="logdb", port=3306, charset="utf8"
            )

        return DBFactory.my_kv[type_name]
Example #3
1
    def read_current(config):
        data = []
        chandata = []

        # Read from DB
        db = MySQLdb.connect("localhost", "monitor", "1234", "smartRectifier")
        curs = db.cursor()
        cmd = "SELECT srIBat FROM sensorDataCurrent ORDER BY srIBat ASC LIMIT 1"
        curs.execute(cmd)
        value = curs.fetchone()
        db.close()
        data.append(value[0])

        # Update data to NULL
        db = MySQLdb.connect("localhost", "monitor", "1234", "smartRectifier")
        curs = db.cursor()
        with db:
            cmd = 'UPDATE sensorDataCurrent SET srIBat = NULL WHERE name = "currentData"'
            curs.execute(cmd)
        db.close()

        for i in range(len(data)):
            chandata.append(
                {
                    "name": "Battery Current",
                    "mode": "float",
                    "unit": "Custom",
                    "customunit": "Amp",
                    "LimitMode": 1,
                    "LimitMaxError": 20.0,
                    "LimitMaxWarning": 15.0,
                    "value": float(data[i]),
                }
            )
        return chandata
Example #4
1
def insertSimToDB(pulseseq, params, dec):
    """ create an entry for a Simulation """

    if not mysql:
        return

    entry_ps = repr(pulseseq.seq)
    entry_params = MySQLdb.escape_string(repr(params.__dict__))
    entry_hspace = MySQLdb.escape_string(repr(params.hspace.__dict__))
    entry_dec = MySQLdb.escape_string(repr(dec.__dict__))

    dbx = MySQLdb.connect(user="tiqcspice", passwd="tiqc_cluster1", db="tiqcspice", host="marvin")
    db = dbx.cursor()

    sql = "insert into Simulation (name, pulseseq, params, hspace, decoherence) values ('%s', '%s','%s','%s','%s')" % (
        dec.doSQLname,
        entry_ps,
        entry_params,
        entry_hspace,
        entry_dec,
    )
    try:
        db.execute(sql)
    except Exception, e:
        print "ERROR in sql insertSimToDB:", e
Example #5
1
def connect():
    if host.startswith("/"):
        conn = MySQLdb.connect(unix_socket=host, user=user, passwd=pw, db=db)
    else:
        conn = MySQLdb.connect(host=host, user=user, passwd=pw, db=db, port=3306)

    return conn
Example #6
1
    def __insert_user_info(self, tweet):
        """ Insert user info into db.
            
            Args:
            tweet: Tweet object containing parsed tweet
        """

        try:
            sql = "INSERT INTO user_info VALUES" "(%d, '%s', '%s', %d, %d, '%s', '%s', '%s', '%s')" % (
                tweet["user"]["user_id"],
                MySQLdb.escape_string(tweet["user"]["screen_name"].encode("utf-8").strip()),
                MySQLdb.escape_string(tweet["user"]["name"].encode("utf-8").strip()),
                tweet["user"]["followers_count"],
                tweet["user"]["friends_count"],
                MySQLdb.escape_string(tweet["user"]["description"].encode("utf-8").strip()),
                MySQLdb.escape_string(tweet["user"]["image_url"].encode("utf-8").strip()),
                tweet["user"]["created_at"].strftime("%Y-%m-%d %H:%M:%S"),
                MySQLdb.escape_string(tweet["user"]["location"].encode("utf-8").strip()),
            )

            self.cursor.execute(sql)

        except Exception, e:
            if "or read-only" not in str(e) and "1062" not in str(e):
                self.print_err("__insert_info", str(e))
Example #7
1
def activate_billing_account(userid, account):
    stmt = (
        "INSERT IGNORE INTO billing_account \
                SET userid = %d, money = %d, income = %d, create_time = NOW()"
        % (userid, account.money, account.money)
    )
    logger.debug(stmt)
    conn.ping(True)
    conn.autocommit(False)
    cur = conn.cursor()
    n = cur.execute(stmt)
    if n == 0:
        stmt = "UPDATE billing_account SET money = money + %d, income = income + %d WHERE userid = %d" % (
            account.money,
            account.money,
            userid,
        )
        logger.debug(stmt)
        cur.execute(stmt)
    stmt = "UPDATE anonymous_account SET flag = 1 WHERE device_id = '%s'" % MySQLdb.escape_string(account.device_id)
    logger.debug(stmt)
    cur.execute(stmt)
    stmt = "UPDATE billing_log SET userid = %d WHERE device_id = '%s'" % (
        userid,
        MySQLdb.escape_string(account.device_id),
    )
    logger.debug(stmt)
    cur.execute(stmt)
    conn.commit()
Example #8
1
    def connect(self):
        if self.currentdb is None or self.config[self.currentdb] is False:
            return False, False

        if not self.currentdb in globalConfig.connection:
            # FIXME: DO NOT TRY HERE
            try:
                globalConfig.connection[self.currentdb] = MySQLdb.connect(
                    host=self.config[self.currentdb]["host"],
                    port=int(self.config[self.currentdb]["port"]),
                    user=self.config[self.currentdb]["user"],
                    passwd=self.config[self.currentdb]["passwd"],
                    db=self.config[self.currentdb]["db"],
                )
                globalConfig.connection[self.currentdb].autocommit(True)
            except Exception, e:
                if "slave_host" in self.config[self.currentdb]:
                    try:
                        globalConfig.connection[self.currentdb] = MySQLdb.connect(
                            host=self.config[self.currentdb]["slave_host"],
                            port=int(self.config[self.currentdb]["slave_port"]),
                            user=self.config[self.currentdb]["slave_user"],
                            passwd=self.config[self.currentdb]["slave_passwd"],
                            db=self.config[self.currentdb]["db"],
                        )
                        globalConfig.connection[self.currentdb].autocommit(True)
                    except Exception, e:
                        if DB.debug is True:
                            print "cannot connect the slave database %s " % e
                        return False, False
                else:
                    if DB.debug is True:
                        print "cannot connect the master database %s no slave" % e
                        return False, False
Example #9
1
def save_character(json_data):
    json_dict = simplejson.loads(json_data)
    username = MySQLdb.escape_string(json_dict["username"])
    world_id = MySQLdb.escape_string(json_dict["world_id"])

    # TODO save the character
    return simplejson.dumps({"success": "true"})
 def generateDBConnector(self, root):
     if root == "QUICKR":
         return MySQLdb.connect(self.QUICKRLOCAL, self.QUICKRROOT, self.QUICKRPASS, self.QUICKRDB)
     if root == "BIKEWALE":
         return MySQLdb.connect(self.BIKEWALELOCAL, self.BIKEWALEROOT, self.BIKEWALEPASS, self.BIKEWALEDB)
     if root == "TEST":
         return MySQLdb.connect(self.QUICKRLOCAL, self.QUICKRROOT, self.QUICKRPASS, self.QUICKRDB)
Example #11
1
    def __createInsertString__(
        self, jobID, pdbID, dateString, scan, stability, binding, metadata, optionArgs, fileArgs
    ):

        """Creates an SQL insert statement based on the provided data.
		
		Note: The table has five columns - JobID, Date, Stability, Scan and Binding.
		The last three are bools which are set to one if the corresponding entry is
		present in the calculations list.
		
		Parameters:
			jobID: The id for the job
			date: The date for the entry as a string in the format yyyy-mm-dd hh:mm:ss"""

        data = SerializeDictionary(metadata)
        data = MySQLdb.escape_string(data)

        optionArgs = SerializeDictionary(optionArgs)
        optionArgs = MySQLdb.escape_string(optionArgs)

        fileArgs = SerializeDictionary(fileArgs)
        fileArgs = MySQLdb.escape_string(fileArgs)

        data = [self.jobTable, jobID, pdbID, dateString, scan, stability, binding, data, optionArgs, fileArgs]

        insertData = (
            """INSERT INTO %s (JobID, PDBID, Date, Scan, Stability, Binding, Metadata, OptionArguments, FileArguments) VALUES ('%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s')"""
            % tuple(data)
        )

        return insertData
def main():
    table_name = sys.argv[1]
    corpus = ReviewCorpus.load("model/" + table_name)

    read_con = MySQLdb.connect(
        host=DB_SERVER, port=DB_SERVER_PORT, user=DB_USER, passwd=DB_PASSWORD, db=DB_NAME, charset="utf8mb4"
    )
    write_con = MySQLdb.connect(
        host=DB_SERVER, port=DB_SERVER_PORT, user=DB_USER, passwd=DB_PASSWORD, db=DB_NAME, charset="utf8mb4"
    )

    read_cursor = read_con.cursor()
    read_cursor.execute(SQL_QUERY % (table_name))

    write_cursor = write_con.cursor()

    for _ in range(read_cursor.rowcount):
        record = read_cursor.fetchone()
        hotel_review = format_review(record[1])
        tfidf_score = 0
        if hotel_review:
            tfidf_score = corpus.tfidf_score(hotel_review)

        write_cursor.execute(SQL_UPDATE % (table_name, tfidf_score, record[0]))
        write_con.commit()
        print "[%f]%s" % (tfidf_score, record[1])

    write_cursor.close()
    write_con.close()

    read_cursor.close()
    read_con.close()
Example #13
1
    def __insert_tweet(self, tweet):
        """ Insert tweet into db.
            
            Args:
            tweet: Tweet object containing parsed tweet
        """

        try:
            sql = "INSERT INTO tweets VALUES" "(%d, '%s', '%s', %d, %d, %d, '%s', %d, %d)" % (
                tweet["tweet"]["tweet_id"],
                MySQLdb.escape_string(tweet["tweet"]["tweet_text"].encode("utf-8").strip()),
                tweet["tweet"]["created_at"].strftime("%Y-%m-%d %H:%M:%S"),
                tweet["tweet"]["geo_lat"],
                tweet["tweet"]["geo_long"],
                tweet["user"]["user_id"],
                MySQLdb.escape_string(tweet["tweet"]["tweet_url"].encode("utf-8").strip()),
                tweet["tweet"]["retweet_count"],
                tweet["tweet"]["original_tweet_id"],
            )

            self.cursor.execute(sql)

        except Exception, e:
            if "or read-only" not in str(e) and "1062" not in str(e):
                self.print_err("__insert_tweet", str(e))
def main_function():
    conn = MySQLdb.connect(host="localhost", user="root", passwd="tanzania", db="twitter_analysis")
    hq_conn = MySQLdb.connect(host="localhost", user="root", passwd="tanzania", db="twitter")

    training_tweets = get_test_tweets(conn)
    training_feature_set = process_tweets(training_tweets)

    classifier = DecisionTreeClassifier.train(training_feature_set)

    test_tweets = get_training_tweets(conn)
    test_feature_set = process_tweets(test_tweets)

    classifier_accuracy = accuracy(classifier, test_feature_set)

    alt_full_matrix = {"+": {"+": 0, "-": 0, "E": 0}, "-": {"+": 0, "-": 0, "E": 0}, "E": {"+": 0, "-": 0, "E": 0}}

    # for f in test_tweets:
    # f = test_tweets[0]

    # print f
    # guess = classifier.classify(process_tweet(f[1]))
    # print guess
    # 	update_tweet_polarity(f[0], guess, conn)
    ##	pl = classifier.prob_classify(process_tweet(f[1]))
    # 	idx = f[2]
    # 	if idx == 'I' or idx == 'O':
    # 		idx = 'E'
    # 	alt_full_matrix[idx][guess] += 1

    # print alt_full_matrix

    print "classifier accuracy: " + repr(classifier_accuracy)
def LogTrace(iso, host, mti, result):

    d = datetime.now()
    hex_dump = dumphex(iso.getNetworkISO())
    iso_dump = iso.dumpFields()

    if result != "":
        trasaction_result = ASResponseCodes.GetISOResponseText(result)
    else:
        trasaction_result = ""

    transaction_type = Tran_Type.GetMessagesescription(mti)

    sql = """ INSERT INTO switch_office.host_trace_log(
                        created,
                        host_data,
                        iso,
                        binary_data,
                        trasaction_result,
                        transaction_type
                        )

            VALUES ("%s", "%s", "%s", "%s", "%s", "%s")
          """ % (
        d,
        host,
        MySQLdb.escape_string(iso_dump),
        MySQLdb.escape_string(hex_dump),
        trasaction_result,
        transaction_type,
    )
    return sql
Example #16
0
    def __insert_urls(self, tweet):
        """ Insert urls into db.
            
            Args:
            tweet: Tweet object containing parsed tweet
        """

        sql = "INSERT INTO tweet_links VALUES(%d, %d, '%s', '%s', '%s')"

        # insert urls
        try:
            for url in tweet["tweet"]["urls"]:
                if url is None or url == "":
                    continue

                tmp_sql = sql % (
                    tweet["tweet"]["tweet_id"],
                    tweet["user"]["user_id"],
                    MySQLdb.escape_string(self.__expand_url(url["expanded_url"])),
                    MySQLdb.escape_string(url["url"]),
                    tweet["tweet"]["created_at"].strftime("%Y-%m-%d %H:%M:%S"),
                )

                self.cursor.execute(tmp_sql)

        except Exception, e:
            if "or read-only" not in str(e) and "1062" not in str(e):
                self.print_err("__insert_urls", str(e))
Example #17
0
File: db.py Project: tuksik/evette
def CheckServer(dbsettings):

    dbip = dbsettings.dbip
    dbuser = dbsettings.dbuser
    dbpass = dbsettings.dbpass

    try:
        if dbpass == "" or dbpass == "None" or dbpass == "False":

            connection = MySQLdb.connect(host=dbip, user=dbuser)

        else:
            connection = MySQLdb.connect(host=dbip, user=dbuser, passwd=dbpass)

        connection.close()
        success = True

        # except MySQLdb.Error, e:
        # print "dbip = ",dbip,", dbuser = ",dbuser,", dbpass = ",dbpass
        # print "db: CheckServer: error: %d: %s " % (e.args[0], e.args[1])
        # success = False

    except:
        success = False

    return success
    def initDb(self, dbConfDict1={}, dbConfDict2={}):
        if len(dbConfDict1) != 0:
            self.dbConfDict1 = dbConfDict1
        if len(dbConfDict2) != 0:
            self.dbConfDict2 = dbConfDict2

        dbConn1 = MySQLdb.connect(
            host=self.dbConfDict1.get("host"),
            user=self.dbConfDict1.get("user"),
            passwd=self.dbConfDict1.get("passwd"),
            port=self.dbConfDict1.get("port"),
            db=self.dbConfDict1.get("db"),
            charset=self.dbConfDict1.get("charset"),
        )
        dbConn2 = MySQLdb.connect(
            host=self.dbConfDict2.get("host"),
            user=self.dbConfDict2.get("user"),
            passwd=self.dbConfDict2.get("passwd"),
            port=self.dbConfDict2.get("port"),
            db=self.dbConfDict2.get("db"),
            charset=self.dbConfDict2.get("charset"),
        )
        cursor1 = dbConn1.cursor()
        cursor2 = dbConn2.cursor()
        self.dbPool["db1"] = cursor1
        self.dbPool["db2"] = cursor2
    def connect_database(self):
        "connects to the mysql database, based on the configuration file with detailed connection to a local/remote host or on a local server with data from my.conf"
        common_parameters = " use_unicode = 'True' , my_conv= {id : int, score : int, content: unicode, title : unicode},cursorclass=MySQLdb.cursors.DictCursor"

        if config.get("server", "use_my_cfg") == True:
            debug_mode("connecting to database with my.cnf", 3)
            self.ttrss_db = MySQLdb.connect(
                host=config.get("server", "host"), db=config.get("server", "database"), read_default_file="~/.my.cnf"
            )

        else:
            debug_mode("connecting to database with login data from config", 3)
            self.ttrss_db = MySQLdb.connect(
                host=config.get("server", "host"),
                user=config.get("server", "user"),
                passwd=config.get("server", "password"),
                db=config.get("server", "database"),
                # port=int (config.get('server','port') )
                use_unicode="True",
                # my_conv= {id : int, score : int, content: unicode, title : unicode},
                cursorclass=MySQLdb.cursors.DictCursor,
            )
        debug_mode("connection established", 3)
        # self.db_cursor = self.ttrss_db.cursor()
        self.db_cursor = self.ttrss_db.cursor()

        self.connected = True
        return self.connected
Example #20
0
def connect_db(host, user, password, dbname):
    """
        (str, str, int, str) -> int
        Given user information, try to connect database.
        Return 0 when success, if the first connection is not successful, try connect 5 times, if failed, return 1 and notify user. 
        Input arguments:
        user: your username
        password: your password
        dbname: the database we are going to log in
        """
    try:
        G.DB = MySQLdb.connect(host=host, user=user, passwd=password, db=dbname)
        print "Database Connected!"
        return 0
    except Exception, err:
        mins = 0
        while mins < 5:
            DB = MySQLdb.connect(host=host, user=user, passwd=password, db=dbname)
            time.sleep(60)
            mins += 1
        print "Fail to connect to database"
        record_log_activity(str(err))
        notify_admin(str(err))
        print traceback.format_exc()
        # Rollback in case there is any error
        DB.rollback()
        return 1
Example #21
0
def report_to_mysql(commit_time, last_commit_hash):
    """Reports the error to mysql"""
    LOG.debug("Report total to MySQL with hall user")
    con = MySQLdb.connect("servcinf", "hall", "hall", "cinfdata")
    cursor = con.cursor()
    query = "INSERT INTO dateplots_hall (time, type, value) VALUES " "(FROM_UNIXTIME(%s), %s, %s)"
    # 164 is pylint errors and 165 is number of lines
    LOG.debug("Using query: {}".format(query))
    LOG.debug("Sending: {}".format((commit_time, 164, sum(ERROR_COUNTER.values()))))
    cursor.execute(query, (commit_time, 164, sum(ERROR_COUNTER.values())))
    LOG.debug("Sending: {}".format((commit_time, 165, TOTAL_LINE_COUNT)))
    cursor.execute(query, (commit_time, 165, TOTAL_LINE_COUNT))
    con.commit()
    con.close()
    LOG.debug("Total number of errors and lines sent to mysql")

    LOG.debug("Report error and file stats to MySQL with pylint user")
    con = MySQLdb.connect("servcinf", "pylint", "pylint", "cinfdata")
    cursor = con.cursor()
    query = "INSERT INTO pylint (time, identifier, isfile, value, commit) " "VALUES (FROM_UNIXTIME(%s), %s, %s, %s, %s)"
    LOG.debug("Using query: {}".format(query))
    # Send error stats
    for key, value in ERROR_COUNTER.items():
        cursor.execute(query, (commit_time, key, False, value, last_commit_hash))
        LOG.debug("Sending: {}".format((commit_time, key, False, value, last_commit_hash)))
    # Send file stats
    for key, value in FILE_COUNTER.items():
        cursor.execute(query, (commit_time, key, True, value, last_commit_hash))
        LOG.debug("Sending: {}".format((commit_time, key, True, value, last_commit_hash)))
    con.commit()
    con.close()
    LOG.debug("Everything else sent to mysql")
def data_import(data_file, table, logger):
    logger.info("importing data from {} into table {}".format(data_file, table))
    connection = mdb.connect(host="localhost", db="FamilyCpolymerases", user="sequser")
    cursor = connection.cursor()
    tables = check_tables(cursor)
    if table not in tables:
        try:
            connection = mdb.connect(host="localhost", db="FamilyCpolymerases", user="sequser")
            cursor = connection.cursor()
            logger.info("table does not exist, creating table %s", table)
            create_table(table, logger)
            time.sleep(1)
            tables = check_tables(cursor)
            if table not in tables:
                logger.debug("why cannot it find the damn table!")
        except mdb.OperationalError:
            pass
    #            logger.error('unable to create table, checking...')
    #            tables = check_tables(cursor)
    #            logger.error('tables in db: {}'.format(str(tables)))
    #            logger.error('skipping to next table')
    #            time.sleep(1)
    #            return
    sql = "load data local infile '{}' ignore into table `{}` fields terminated by '\t' (Header, Sequence)"
    logger.debug("load data statement for {}: {}".format(table, sql.format(data_file, table)))
    cursor.execute(sql.format(data_file, table))
    connection.commit()
    connection.close()
    logger.info("data imported into table %s", table)
    return extract_accession_numbers(table, logger)
Example #23
0
File: db.py Project: tuksik/evette
def GetConnection(dbsettings, dbname="evette"):

    dbip = dbsettings.dbip
    dbuser = dbsettings.dbuser
    dbpass = dbsettings.dbpass

    try:
        if dbpass == "" or str(dbpass) == "None" or str(dbpass) == "False":
            connection = MySQLdb.connect(host=dbip, user=dbuser, db=dbname)
        else:
            connection = MySQLdb.connect(host=dbip, user=dbuser, db=dbname, passwd=dbpass)

        try:
            connection.set_character_set("utf8")
        except:
            pass

            # except MySQLdb.Error, e:

            # print "db: GetConnection: error: %d: %s " % (e.args[0], e.args[1])
            # success = False
            # connection = False

    except:
        connection = False

    return connection
Example #24
0
File: db.py Project: tuksik/evette
def CreateDatabase(dbsettings):

    success = False
    dbip = dbsettings.dbip
    dbuser = dbsettings.dbuser
    dbpass = dbsettings.dbpass

    if dbpass == "" or dbpass == "None" or dbpass == "False":

        connection = MySQLdb.connect(host=dbip, user=dbuser)

    else:
        connection = MySQLdb.connect(host=dbip, user=dbuser, passwd=dbpass)

    action = "CREATE DATABASE evette"
    SendSQL(action, connection)
    connection.close()

    connection = GetConnection(dbsettings)

    # print "Creating all tables"

    dbmethods.CreateAllTables(connection)

    # print "All tables created"

    connection.close()

    success = True

    return success
def main():
    conn = MySQLdb.connect(
        host=settings.MYSQL_HOST, user=settings.MYSQL_USER, passwd=settings.MYSQL_PASSWORD, db=settings.MYSQL_DATABASE
    )
    cursor = conn.cursor()

    TABLES = ["trips"]

    for table in TABLES:
        print "processing %s" % table
        f = open("gtfs/%s.txt" % table, "r")
        reader = csv.reader(f)
        columns = reader.next()
        for row in reader:
            insert_row = []
            for value in row:
                if not is_numeric(value):
                    insert_row.append('"' + MySQLdb.escape_string(value) + '"')
                else:
                    insert_row.append(value)

            insert_sql = "INSERT INTO %s (%s) VALUES (%s);" % (table, ",".join(columns), ",".join(insert_row))
            cursor.execute(insert_sql)

        cache_sql = "UPDATE stop_times SET stop_times.departure_time_seconds = 3600*CAST(SUBSTRING(stop_times.departure_time FROM 1 FOR 2) AS UNSIGNED) + 60*CAST(SUBSTRING(stop_times.departure_time FROM 4 FOR 2) AS UNSIGNED) + CAST(SUBSTRING(stop_times.departure_time FROM 7 FOR 2) AS UNSIGNED)"
    cursor.execute(cache_sql)
    cache_sql = "UPDATE stop_times SET stop_times.arrival_time_seconds = 3600*CAST(SUBSTRING(stop_times.arrival_time FROM 1 FOR 2) AS UNSIGNED) + 60*CAST(SUBSTRING(stop_times.arrival_time FROM 4 FOR 2) AS UNSIGNED) + CAST(SUBSTRING(stop_times.arrival_time FROM 7 FOR 2) AS UNSIGNED)"
    cursor.execute(cache_sql)
    cursor.close()
    conn.close()
Example #26
0
    def __make_update_sqls(self, data_ls):
        if not data_ls:
            return

        strip_dic_list(data_ls)

        values_ls = []
        for data in data_ls:
            assert self.key_field in data

            set_str_ls = []
            for field, value in data.items():
                if field not in self.field_info_dic:
                    continue

                field_info = self.field_info_dic[field]

                if field == self.key_field:
                    continue

                if is_db_string(field_info["DATA_TYPE"]):
                    set_str_ls.append("%s='%s'" % (field, MySQLdb.escape_string(str(value))))
                else:
                    set_str_ls.append("%s=%s" % (field, value))

            kft = self.field_info_dic[self.key_field]["DATA_TYPE"]
            key_str = ("%s='%s'" if kft else "%s=%d") % (
                self.key_field,
                MySQLdb.escape_string(str(data[self.key_field])),
            )
            set_str = ",".join(set_str_ls)
            values_ls.append("UPDATE %s SET %s WHERE %s" % (self.table_name, set_str, key_str))
        return values_ls
Example #27
0
def insertMySQL(tweetDict):
    con = None
    try:
        con = mdb.connect("localhost", "root", "sa", "tweetsearch")
        cur = con.cursor()
        with con:
            cur = con.cursor()
            checkquery = (
                "SELECT * FROM tweet WHERE username = '"
                + MySQLdb.escape_string(tweetDict.keys()[0])
                + "' AND tweetcontent = '"
                + MySQLdb.escape_string(tweetDict.get(tweetDict.keys()[0]))
                + "'"
            )
            cur.execute(checkquery)
            rows = cur.fetchall()
            if len(rows) == 0:
                query = (
                    "INSERT INTO tweet(username, tweetcontent) VALUES('"
                    + MySQLdb.escape_string(tweetDict.keys()[0])
                    + "', '"
                    + MySQLdb.escape_string(tweetDict.get(tweetDict.keys()[0]))
                    + "')"
                )
                cur.execute(query)
                return True
    except mdb.Error, e:
        print "Error %d: %s" % (e.args[0], e.args[1])
        sys.exit(1)
Example #28
0
File: io.py Project: haehn/nipype
    def _list_outputs(self):
        """Execute this module.
        """
        import MySQLdb

        if isdefined(self.inputs.config):
            conn = MySQLdb.connect(db=self.inputs.database_name, read_default_file=self.inputs.config)
        else:
            conn = MySQLdb.connect(
                host=self.inputs.host,
                user=self.inputs.username,
                passwd=self.inputs.password,
                db=self.inputs.database_name,
            )
        c = conn.cursor()
        c.execute(
            "REPLACE INTO %s (" % self.inputs.table_name
            + ",".join(self._input_names)
            + ") VALUES ("
            + ",".join(["%s"] * len(self._input_names))
            + ")",
            [getattr(self.inputs, name) for name in self._input_names],
        )
        conn.commit()
        c.close()
        return None
def make_corrected_epoch(dev_id, date_1, time_1, date_2, time_2):
    # Make lists of desired values
    info_1 = date_1.split("-") + time_1.split(":")
    info_2 = date_2.split("-") + time_2.split(":")

    # Convert times/dates into epoch time
    time1 = calendar.timegm((int(info_1[0]), int(info_1[1]), int(info_1[2]), int(info_1[3]), int(info_1[4]), 0))

    time2 = calendar.timegm((int(info_2[0]), int(info_2[1]), int(info_2[2]), int(info_2[3]), int(info_2[4]), 0))

    # Find UTC offset of device
    if os.getenv("SERVER_SOFTWARE", "").startswith("Google App Engine"):
        db = MySQLdb.connect(unix_socket="/cloudsql/bloomsky-backend-test:bloomsky", db="bloomsky", user="root")
    else:
        db = MySQLdb.connect(host="173.194.248.186", port=3306, user="wei", passwd=">lO9*iK,", db="bloomsky")
    cursor = db.cursor()
    cursor.execute("SELECT DISTINCT UTC FROM bloomsky.appi_skydevice WHERE DeviceID='" + str(dev_id) + "';")
    offset = int(cursor.fetchone()[0])
    db.close()

    # # Find UTC offset of device
    # conn = rethinkdb.connect(host='23.236.61.183',port=28015,db='bloomsky',auth_key='5Hx8X}Rx%6g`\rHkBXj+')
    # cursor = rethinkdb.table("skydata").filter({"DeviceID": dev_id}).order_by(rethinkdb.desc("TS")).limit(1).pluck("UTC").run(conn)
    # offset = int(cursor[0]["UTC"])
    # conn.close()

    # Correct time for UTC offset
    time1 = time1 - (int(offset) * 60 * 60)
    time2 = time2 - (int(offset) * 60 * 60)

    return [dev_id, time1, time2, int(offset)]
Example #30
0
    def delete_ul(self, name="ul_live"):
        find_list_sql = 'select l.list_id from list l where l.list_name like "' + name + '%"'
        conn = MySQLdb.connect(self.__dbHost, self.__dbUser, self.__dbPass, self.global_db_name, charset="utf8")
        to_delete_list_id = [str(i[0]) for i in exe_sql(conn, find_list_sql, False)]

        in_param = ",".join(to_delete_list_id)

        del_lc_sql = "delete from list_column where list_id in (" + in_param + ");"
        del_lf_sql = "delete from list_format where list_id in (" + in_param + ");"
        del_l_sql = "delete from list where list_id in (" + in_param + ");"
        exe_sql(conn, del_lc_sql, False)
        exe_sql(conn, del_lf_sql, False)
        exe_sql(conn, del_l_sql, False)
        conn.commit()
        close_conn(conn)
        #        print del_lc_sql;
        #        print del_lf_sql;
        #        print del_l_sql;

        list_conn = MySQLdb.connect(
            self.__list_db_host, self.__dbUser, self.__dbPass, self.list_db_name, charset="utf8"
        )
        del_subcribe_sql = [exe_sql(list_conn, "drop table l%s_subscriber;" % (id), False) for id in to_delete_list_id]
        list_conn.commit()
        close_conn(list_conn)

        print in_param