def updateCrawl(self,givenid):
query = PySQLPool.getNewQuery(self._db)
#mysql_entry = self._db.cursor(MySQLdb.cursors.DictCursor)
query.Query("update `mc_caching` set `last_crawl`=now() WHERE `id`='"+str(givenid)+"'")
PySQLPool.commitPool()
return
def commitPool(self):
'''
Actively commit all transactions in the entire PySQLPool
'''
PySQLPool.commitPool()
def update_data(self):
query = PySQLPool.getNewQuery(self._db)
query.execute("UPDATE `mc_versioning` SET `aktiv`=0 WHERE `mc_caching_id`=%s", self._entry)
PySQLPool.commitPool()
def gotConnection(conn, username, password):
#print "Connected to broker."
yield conn.authenticate(username, password)
print "Authenticated. Ready to receive messages"
chan = yield conn.channel(1)
yield chan.channel_open()
yield chan.queue_declare(queue="someQueueName")
# Bind to submit.sm.* and submit.sm.resp.* routes
yield chan.queue_bind(queue="someQueueName",
exchange="messaging",
routing_key='submit.sm.*')
yield chan.queue_bind(queue="someQueueName",
exchange="messaging",
routing_key='submit.sm.resp.*')
yield chan.basic_consume(queue='someQueueName',
no_ack=True,
consumer_tag="someTag")
queue = yield conn.queue("someTag")
#Build Mysql connection pool
PySQLPool.getNewPool(
).maxActiveConnections = 20 #Set how many reusable conns to buffer in the pool
print "Pooling 20 connections"
#Connection parameters - Fill this info with your MySQL server connection parameters
mysqlconn = PySQLPool.getNewConnection(username='******',
password='******',
host='server_host',
db='database_name')
print "Connected to MySQL"
queryp = PySQLPool.getNewQuery(mysqlconn)
# Wait for messages
# This can be done through a callback ...
while True:
msg = yield queue.get()
props = msg.content.properties
pdu = pickle.loads(msg.content.body)
if msg.routing_key[:15] == 'submit.sm.resp.':
#print 'SubmitSMResp: status: %s, msgid: %s' % (pdu.status,
# props['message-id'])
#Update a record in mysql according to your own table. This will fire upon receiving a PDU response.
#Make sure you already have a matching sms record to update.
queryp.Query(
"UPDATE table_name SET status='%s' WHERE messageid='%s'" %
(pdu.status, props['message-id']))
PySQLPool.commitPool(
) #Very important, always execute a commit, autocommit doesn´t work well here
elif msg.routing_key[:10] == 'submit.sm.':
#print 'SubmitSM: from %s to %s, content: %s, msgid: %s' % (pdu.params['source_addr'],
# pdu.params['destination_addr'],
# pdu.params['short_message'],
# props['message-id'])
# This will fire every time a message is sent to the SumbitSM queue.
# Create a record with the messagesent msg
queryp.Query(
"INSERT INTO table_name (messageid,carrier,date,dst,src,status,accountcode,cost,sale,plan_name,amaflags,content) VALUES ('%s','Carrier',NOW(),'%s','%s','8','00000','0.0','0.0','plan_name','some_status','%s') "
% (props['message-id'], pdu.params['destination_addr'],
pdu.params['source_addr'], pdu.params['short_message']))
"""
The previous query works for the following table structure:
id INT primary_key auto_increment
messageid VARCHAR(128)
carrier VARCHAR
date DATETIME
dst VARCHAR(15)
src VARCHAR(15)
status VARCHAR(10)
accountcode INT
cost FLOAT
sale FLOAT
plan_name VARCHAR(25)
amaflags VARCHAR(10)
content VARCHAR(160)
"""
PySQLPool.commitPool() # Remember to Commit
else:
print 'unknown route'
# A clean way to tear down and stop
yield chan.basic_cancel("someTag")
yield chan.channel_close()
chan0 = yield conn.channel(0)
yield chan0.connection_close()
reactor.stop()
def insert_data(self):
self.update_data()
query = PySQLPool.getNewQuery(self._db)
query.execute("INSERT INTO `mc_versioning` (`mc_caching_id`, `mc_name`, `mc_skin_path`, `mc_skin_all_uuid`, `mc_skin_all_md5`, `flag_delete`, `aktiv`) VALUES (%s,%s,%s,%s,%s,%s,%s)", (self._entry, self._write_into_db['mc_name'], self._write_into_db['mc_skin_path'], self._write_into_db['mc_skin_all_uuid'], self._write_into_db['mc_skin_all_md5'], 0, 1))
PySQLPool.commitPool()