Re: How to handle sockets - easily?
 

Richard Kettlewell's log on stardate 10 vlj 2011

> Rewrites can help but they can also go badly wrong. Rewriting into a
> language you don't yet know seems especially likely to fall into the
> latter category!

Indeed.

However, it seems to be quite "doable", as it took me from 0 knowledge
of Python to come up with a solution that does only part of the thing
(it's missing CRC check for GPS data and NMEA checksum, user control
for changing device setup on the fly via web and few more details).

Here's the code (ATTN: may wrap):

#! /usr/bin/env python

import asyncore
import socket
import string
import MySQLdb
import sys

class Handler(asyncore.dispatcher_with_send):

def handle_read(self):
data = self.recv(128) #payload size

data = string.lstrip(data,"\n") #sometimes a 0xOA stays
data_len = len (data) #get payload length
trackerID_hex = data[4:11] #tracker ID
trackerID = "" #create empty string
for i in trackerID_hex:
trackerID += str('%02X'%ord(i)) #convert hex integer to string and append to trackerID
trackerID = string.rstrip(trackerID,"F") #remove stuffed F's from tracker ID
checksum = ""
checksum_hex = data[data_len-4:data_len-1] #second part of command payload, checksum
for i in checksum_hex:
checksum += str('%02X'%ord(i)) #convert hex integer to string and append to trackerID
GPSdata = data[13:data_len-4] #GPRMC - hhmmss.dd,S,xxmm.dddd,<N|S>,yyymm.dddd,<E|W>,s.s,h .h,ddmmyy
l = list()
l = string.split(GPSdata, ",")
n = len(l)
if n > 1: #hack! devices can mess the output, so just discard the data and go to another sample
GPStime = l[0]
GPStime = GPStime[0:2] + ":" + GPStime[2:4] + ":" + GPStime[4:6]
GPSstatus = l[1]
GPSlatitude = l[2]
GPSlatitude = float(GPSlatitude[0:2]) + float(GPSlatitude[2:10]) / 60
GPSNS = l[3]
GPSlongitude = l[4]
GPSlongitude = float(GPSlongitude[0:3]) + float(GPSlongitude[3:11]) / 60
GPSEW = l[5]
try:
GPSspeed = float(l[6])
except ValueError:
GPSspeed = 0
GPSspeed *= 1.852
try:
GPSheading = float(l[7])
except ValueError:
GPSheading = 0
GPSdate = l[8]
GPSdate = "20" + GPSdate[4:6] + "-" + GPSdate[2:4] + "-" + GPSdate[0:2]
try:
conn = MySQLdb.connect (host = "localhost", user = "", passwd = "", db = "") #:p
except MySQLdb.Error, e:
print "Error %d: %s" % (e.args[0], e.args[1])
sys.exit (1)
cursor = conn.cursor()
query = "INSERT INTO data (trackerID, GPStime, GPSstatus, GPSlatitude, GPSNS, GPSlongitude, GPSEW, GPSspeed, GPSheading, GPSdate) VALUES ('" + trackerID + "', '" + GPStime + "', '" + GPSstatus + "', '" + str(GPSlatitude) + "', '" + GPSNS + "', '" + str(GPSlongitude) + "', '" + GPSEW + "', '" + str(GPSspeed) + "', '" + str(GPSheading) + "', '" + GPSdate + "')"
cursor.execute (query)
cursor.close ()
conn.commit()
conn.close ()

class Server(asyncore.dispatcher):

def __init__(self, host, port):
asyncore.dispatcher.__init__(self)
self.create_socket(socket.AF_INET, socket.SOCK_STREAM)
self.set_reuse_addr()
self.bind((host, port))
self.listen(5)

def handle_accept(self):
pair = self.accept()
if pair is None:
pass
else:
sock, addr = pair
print 'Incoming connection from %s' % repr(addr)
handler = Handler(sock)

server = Server('', 2020)
asyncore.loop()

C code did not parse data, but rather put the whole payload to SQL
database, which would be parsed afterwards. This code takes almost
twice LOC less that C code.

I do, however, have some more questions (thus crosspost to
comp.lang.python) - how many connections can this server handle without
a problem? I'm using Asyncore module, as it can be seen.

Is it necessary, due to the fact that it should serve more than
thousand devices that send data every 10 seconds, to do threading (I
believe that is already done with Asyncore for sockets, but what about
SQL?)

Any other general code suggestions (since this is the first time I
write anything in Python)?

TIA!

--
"If you lie to the compiler,
it will get its revenge."
Henry Spencer
2.718281828459045235360287471352662497757247093699 959574966967627.com

Re: How to handle sockets - easily?
 

William Ahern's log on stardate 16 vlj 2011

/snip

> I think that there's an asynchronous all-Python MySQL library, but
> I'm not sure. Maybe one day I can open source my asynchronous MySQL C
> library. (I always recommend people to use PostgreSQL, though; which
> is superior in almost every way, especially the C client library and
> the wire protocol.)

I have no particular problem with using PgSQL.

Which of these would you recommend? http://wiki.postgresql.org/wiki/Python

--
"If you lie to the compiler,
it will get its revenge."
Henry Spencer
2.718281828459045235360287471352662497757247093699 959574966967627.com

Re: How to handle sockets - easily?
 

On Feb 16, 1:59*pm, William Ahern <will...@wilbur.25thandClement.com>
wrote:
> Bubba <nickn...@banelli.biz.invalid> wrote:
>
> <snip>
>
>
>
>
>
>
>
>
>
> > import asyncore
> > import socket
> > import string
> > import MySQLdb
> > import sys
> <snip>
> > * * def __init__(self, host, port):
> > * * * * asyncore.dispatcher.__init__(self)
> > * * * * self.create_socket(socket.AF_INET, socket.SOCK_STREAM)
> > * * * * self.set_reuse_addr()
> > * * * * self.bind((host, port))
> > * * * * self.listen(5)
>
> > * * def handle_accept(self):
> > * * * * pair = self.accept()
> > * * * * if pair is None:
> > * * * * * * pass
> > * * * * else:
> > * * * * * * sock, addr = pair
> > * * * * * * print 'Incoming connection from %s' % repr(addr)
> > * * * * * * handler = Handler(sock)
>
> > server = Server('', 2020)
> > asyncore.loop()
>
> <snip>
> > I do, however, have some more questions (thus crosspost to
> > comp.lang.python) - how many connections can this server handle without
> > a problem? I'm using Asyncore module, as it can be seen.
> > Is it necessary, due to the fact that it should serve more than
> > thousand devices that send data every 10 seconds, to do threading (I
> > believe that is already done with Asyncore for sockets, but what about
> > SQL?)
>
> The MySQL C library is not asynchronous. Each request does blocking I/O. If
> the MySQLdb module is a wrapper for the MySQL C library--and it seems it
> is--then you will want to use threads (not coroutines or generators). For
> all I know your accept handler is threaded already. MySQL itself almost
> certainly can't handle tens of thousands of simultaneous requests. The
> backend connection and query handler is simply-threaded as well, which means
> for every connection your talking 2 * "tens of thousands" threads. I didn't
> read over your code much, but the only way to get around this would be to
> handle your socket I/O asynchronously; but I don't enough about Python to
> get the mixed behavior you'd want.
>
> I think that there's an asynchronous all-Python MySQL library, but I'm not
> sure. Maybe one day I can open source my asynchronous MySQL C library. (I
> always recommend people to use PostgreSQL, though; which is superior in
> almost every way, especially the C client library and the wire protocol.)

There's the very new <https://github.com/hybridlogic/txMySQL>.
There's also
<http://pypi.python.org/pypi/txpostgres>.

Jean-Paul

PostgreSQL vs MySQL (was Re: How to handle sockets - easily?)
 

In article <fdjt28-flo.ln1@wilbur.25thandClement.com>,
William Ahern <william@wilbur.25thandClement.com> wrote:
>
>I think that there's an asynchronous all-Python MySQL library, but I'm not
>sure. Maybe one day I can open source my asynchronous MySQL C library. (I
>always recommend people to use PostgreSQL, though; which is superior in
>almost every way, especially the C client library and the wire protocol.)

Can you point at a reference for the latter? I have been trying to
convince my company that PG is better than MySQL.
--
Aahz (aahz@pythoncraft.com) <*> http://www.pythoncraft.com/

"Beware of companies that claim to be like a family. They might not be
lying." --Jill Lundquist

Re: PostgreSQL vs MySQL
 

In article <87bp1a3g59.fsf@benfinney.id.au>,
Ben Finney <ben+python@benfinney.id.au> wrote:
>aahz@pythoncraft.com (Aahz) writes:
>
>>>(I always recommend people to use PostgreSQL, though; which is
>>>superior in almost every way, especially the C client library and the
>>>wire protocol.)
>>
>> Can you point at a reference for the latter? I have been trying to
>> convince my company that PG is better than MySQL.
>
>These may be helpful:
>
> <URL:http://wiki.postgresql.org/wiki/Why_PostgreSQL_Instead_of_MySQL_2009>
> <URL:http://www.wikivs.com/wiki/MySQL_vs_PostgreSQL>

Thanks! I've forwarded this.

However, it doesn't address the assertion about the client library and
wire protocol.
--
Aahz (aahz@pythoncraft.com) <*> http://www.pythoncraft.com/

"Beware of companies that claim to be like a family. They might not be
lying." --Jill Lundquist

Re: PostgreSQL vs MySQL (was Re: How to handle sockets - easily?)
 

On Mar 16, 10:19*am, a...@pythoncraft.com (Aahz) wrote:
> In article <fdjt28-flo....@wilbur.25thandClement.com>,

> >always recommend people to use PostgreSQL, though; which is superior in
> >almost every way, especially the C client library and the wire protocol.)
>
> Can you point at a reference for the latter? *I have been trying to
> convince my company that PG is better than MySQL.
> --

Well, my $.02 worth is that, about 3 yrs ago, on 5.0x-5.1x, I was
truly appalled by the sheer level of stupidity displayed by MySQL's
handling of a moderately complex UPDATE SQL query with a correlated
subquery.

(Let's say this was a 7 out of 10 complexity, with your standard
selects being 1-3 max and a nightmare update query with all sorts of
correlated subqueries would be a 9. I am first of all a database
programmer, so queries are my world).

Not only did MySQL mangle the query because it didn't understand what
I was asking, it thrashed the data during the update and committed
it. And, when I reviewed the query once again, I found I had
mismatched parentheses, so it wasn't even syntaxically correct. Truly
scary.

DB2 + SQLBase punted for years on correlated subqueries Ex: "update
ORDERS where x=y and exists (select 1 from ORDERS where <some
condition>)". The DB2 engine doesn't know how to handle them, so it
tells you to get lost.

MySQL is not smart enough to recognize it's over its head and instead
makes a best effort. To me it looks like a database that will get you
80% there and steadfastly refuse the last 20%, assuming you need
really clever queries. PG was much cleaner in behavior, though a pain
to install, especially on Windows.