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
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]
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
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
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
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))
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()
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
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)
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()
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
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))
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
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
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)
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
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()
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
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)
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)]
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