OpenALPR Daemon Data to database

[Emanuel Hernandez]

Hello everyone!

I am trying to post all the data found by OpenALPR to a MySQl database.  Can some one give me examples on how to get this done? 

[Ron]

I have a python script I run that drains the beanstalk queue and inserts selected information into at MySQL database. It runs once a minute using a cron job. I am not a programmer and I am sure there are better ways to do this, but it is working well for me.

#!/usr/bin/env python

import beanstalkc

import time

import json

import MySQLdb

db = MySQLdb.connect("localhost", "root", "YOUR_MySQL_PASSWORD","alprdb")

beanstalk = beanstalkc.Connection(host='localhost', port=11300)

cur = db.cursor()

beanstalk.watch('alprd')

while True:

        job = beanstalk.reserve(timeout=0)

        if job is not None:

                decoded= json.loads(job.body)

                _plate = decoded['results'][0]['plate']

                _uuid = decoded['uuid']

                epoch = decoded['epoch_time']

                _date = time.strftime('%Y-%m-%d',time.localtime(epoch/1000))

                _time = time.strftime('%H:%M:%S',time.localtime(epoch/1000))

                _location = decoded['site_id']

                _camera = decoded['camera_id']

                cur.execute("INSERT INTO alprdb(date, time, location, camera, plate, uuid) VALUES(%s, %s, %s, %s, %s, %s)",(_date, _time, _location, _camera, _plate, _uuid))

                db.commit()

                job.delete()

else:

cur.close()

db.close()

break

exit()

[Emanuel Hernandez]

Hi Ron,

Thank you for sharing your code. I modified Steve Allie code and added the SQL insert statements (below). Do you know how to capture multiple streams each with unique site ids? 

#!/usr/bin/python

import MySQLdb

import beanstalkc

import datetime

import json

import os

import time

import csv

conn = MySQLdb.connect(host="localhost",user="lpr",passwd="B1ack53@",db="lpr")

c=conn.cursor()

os.system("/home/anpr/openalpr-video/src/build/alprd -c eu -n 1 & ")

whitelist = ["your plate here"]

beanstalk = beanstalkc.Connection(host='localhost', port=11300)

beanstalk.watch('alprd')

def opengate():

   print ("Opening gate.")

   os.system("sudo echo -e '\xff\x01\x01' > /dev/ttyUSB0") #on

   time.sleep(1)

   os.system("sudo echo -e '\\xff\\x01\\x00' > /dev/ttyUSB0") #off

   print ("Gate Opened")

   time.sleep(45)

   emptybeanstalk();

def emptybeanstalk():

   print ("Emptying beanstalk.")

   s = beanstalk.stats_tube("alprd")

   ss = s['current-jobs-ready']

   while (ss > 0):

       bos = beanstalk.reserve()

       bos.delete()

       ss -= 1

   beanstalk.watch('alprd')

   print ("Beanstalk emptied.")

   print ("Watching gate.")

def main():

   emptybeanstalk();

   while True:

       job = beanstalk.reserve()

       jobb = job.body

       job.delete()

       d = json.loads(jobb)

       tm=time.strftime('%X %Z')

       day=time.strftime('%x')

       epoch = d['epoch_time']

      # realtime = datetime.datetime.fromtimestamp(epoch).strftime('%Y-%m-%d %H:%M:%S')

       realtime=1

       proctime = d['processing_time_ms']

       plate = d['results'][0]['plate']

       confid = d['results'][0]['confidence']

       uuid = d['uuid']

       plates = open('/home/manny/plates.csv', 'a')

      #s = (str(time) + ", "+ str(day)+ ", "  + str(plate) + ", " + str(confid) + ", " + str(uuid) + "\n")

      #plates.write(s)

      #plates.close()

      #print s

       sql="INSERT INTO liccaptured(realtime, proctime, plate, confid, uuid, time, date) VALUES (%s,%s,%s,%s,%s,%s,%s)"

       sqlparams=realtime,proctime,plate,confid,uuid,tm,day

       c.execute(sql,sqlparams)

       conn.commit()

       #if plate in whitelist:

       #    opengate()

if __name__ == "__main__":

   main()

[Ron]

That is something I have thought about but not yet tried. For my intended application I was going to try and run a separate Linux virtual machine for each site id and another separate VM to run the database. If anyone else has other thoughts I would like to hear them.

Ron

[Scott Plude]

I realize this thread has been idle for several years but do you know how the mysql database was created? a script? do you have a blank db to import?

[Nigel Andam]

What camera did you used for this beanstalk? Can I use this even without using the Cloud Stream?

[soheeb uddin syed]

hi emannuel i am getting an erro if i use your code,

ss = s['current-jobs-ready']

string indices must be integer not str

how can i fix this? i am new to python

 

[Nadzim]

i cannot find location this location (home/anpr/openalpr-video/src/build/alprd ) can you explain to me