数据库中的Jasmin SMSC网关/源连接器

Question

我安装了Jasmin SMSC网关，它工作得很好。你知道吗

现在我正在尝试将短信记录到mysql数据库中。 为此，我使用以下脚本从RabbitMQ队列获取消息：

# -*- coding: utf-8 -*-
import pickle
from twisted.internet.defer import inlineCallbacks
from twisted.internet import reactor
from twisted.internet.protocol import ClientCreator
from twisted.python import log

from txamqp.protocol import AMQClient
from txamqp.client import TwistedDelegate

import txamqp.spec

#Mysql conn pool handler
import PySQLPool


@inlineCallbacks
def gotConnection(conn, username, password):
    #print "Connected to broker."
    yield conn.authenticate(username, password,'PLAIN')

    print "Authenticated. Ready to receive messages"
    chan = yield conn.channel(1)
    yield chan.channel_open()

    yield chan.queue_declare(queue="someQueueName10")

    # Bind to submit.sm.* and submit.sm.resp.* routes
    yield chan.queue_bind(queue="someQueueName10", exchange="messaging", routing_key='submit.sm.*')
    yield chan.queue_bind(queue="someQueueName10", exchange="messaging", routing_key='deliver.sm.*')
    yield chan.queue_bind(queue="someQueueName10", exchange="messaging", routing_key='submit.sm.resp.*')

    yield chan.basic_consume(queue='someQueueName10', 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='jasmin_db',
        password='jasmindb',
        host='127.0.0.1',
        db='jasmin_db')

    print "Connected to MySQL"
    queryp = PySQLPool.getNewQuery(mysqlconn)

    # Wait for messages
    # This can be done through a callback ...
    while True:
        print 'test1'
        msg = yield queue.get()
        props = msg.content.properties
        pdu = pickle.loads(msg.content.body)
        print 'test'
        print '%s' % (msg.routing_key)
        if msg.routing_key[:15] == 'submit.sm.resp.':
                print 'SubmitSMResp: status: %s, msgid: %s' % (pdu.status,
                       props['message-id'])
                queryp.Query("UPDATE table_name SET status='%s' WHERE messageid='%s'" % (pdu.status,props['message-id']))

                PySQLPool.commitPool() 
        elif msg.routing_key[:10] == 'submit.sm.':

                print 'SubmitSM: from %s to %s, content: %s, msgid: %s supp %s ' % (pdu.params['source_addr'],
                       pdu.params['destination_addr'],
                       pdu.params['short_message'],
                       props['message-id'],
                       pdu.params['source_addr']
                       )

                queryp.Query("INSERT INTO cdrs (messageid,carrier,date,dst,src,status,accountcode,cost,sale,plan_name,amaflags,content) VALUES ('%s','%s',NOW(),'%s','%s','%s','00000','0.0','0.0','plan_name','some_status','%s') " % (props
['message-id'],msg.routing_key.replace("submit.sm.",""), pdu.params['destination_addr'], pdu.params['source_addr'],pdu.status, pdu.params['short_message']) )



                PySQLPool.commitPool() 
        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()


if __name__ == "__main__":


    host = '127.0.0.1'
    port = 5672
    vhost = '/'
    username = 'guest'
    password = 'guest'
    spec_file = '/etc/jasmin/resource/amqp0-9-1.xml'

    spec = txamqp.spec.load(spec_file)

    # Connect and authenticate
    d = ClientCreator(reactor,
        AMQClient,
        delegate=TwistedDelegate(),
        vhost=vhost,
        spec=spec).connectTCP(host, port)
    d.addCallback(gotConnection, username, password)

    def whoops(err):
        if reactor.running:
            log.err(err)
            reactor.stop()

    d.addErrback(whoops)

    reactor.run()

我能够将消息保存在数据库中，但是我需要一种方法来获取源连接器或发送消息的用户，并将其保存在数据库中。 有办法实现吗？你知道吗