This project consists in creating a helpful tool to manage the equipment at the storage in the electrical engineering building of the University of Costa Rica.

We use the open source relational database management system (RDBMS) MySql and the programming language Python, the first one to create a database that contains all the equipment and users, also we use the open source software suite ZBar for reading bar codes from various sources and Python for manage the ZBar functions and control the database.

General Objective

• Implement a basic program that helps with the manage of the equipment of certain place

Specific Objectives

• Learn to use MySQL.
• Create a program using Python to edit and manipulate the database.
• Get to know with the open source software like ZBar.

Scanning and decoding barcode with webcam (zbarcam)
Creating and Managing Databases in MySQL
Relating Python with MySQL

Table Equipment

Table User

we use the following code to define a function that returns the scan code as a integer value and use this value for the application. We use the module scan to get the value and access to our database.

  #!/usr/bin/python
 
  import zbar
 
  def scan():
 
	proc = zbar.Processor()
 
	proc.parse_config('enable')
 
	device = '/dev/video0'
	proc.init(device)
 
	proc.visible = True
	proc.process_one()
	proc.visible = False
 
	for symbol in proc.results:
		code = [symbol.data]
		code=code[0]
		code=int(code)
		return code

In the last lines of this script you can see how we extract from the list symbol.data the integer value of the scan code.

We use the next code to build the update module. which receives two parameters through the tuple data, which contains the variables user and equipment, scanned with the scan module

#!/usr/bin/python
 
import MySQLdb
 
def updatedb(data):
	# Open database connection
	db = MySQLdb.connect("localhost","user","bodega","bodegaEIE" )
 
	# prepare a cursor object using cursor() method
	cursor = db.cursor()
 
	# Prepare SQL query to UPDATE required records
	sql = "UPDATE equipo SET usuario = '%s' WHERE placa = '%s'" %(data)
	try:
	   # Execute the SQL command
	   cursor.execute(sql)
	   # Commit your changes in the database
	   db.commit()
	except:
	   # Rollback in case there is any error
	   db.rollback()
 
	# disconnect from server
	db.close()

This module was created to verify a certain equipment is registered in the database. Note how the fetchall method returns a list of tuples or empty list when no rows available. This module return a False boolean value to indicate that some equipment is not registered in the database

#!/usr/bin/python
# -*- coding: utf-8 -*-
 
import MySQLdb as mdb
import sys
 
def checkdb(code):
 
	# Open database connection	
	db= mdb.connect('localhost', 'user', 'bodega', 'bodegaEIE')
 
	# prepare a cursor object using cursor() method
	cursor = db.cursor()
 
	# Prepare SQL query to INSERT a record into the database.
	sql = "SELECT * FROM equipo WHERE placa = '%d'" % (code)
	try:
		# Execute the SQL command
		cursor.execute(sql)
		# Fetch all the rows in a list of lists.
		results = cursor.fetchall()
		if len(results)  == 0:
			return False
		else:
			return True
	except:
		print "Error: unable to fecth data"
 
	# disconnect from server
	db.close()

This one was created to access the database and take the information for a determined equipment.

#!/usr/bin/python
 
import MySQLdb
 
def readdb(code):
 
	# Open database connection
	db = MySQLdb.connect("localhost","root","cocolocoEC85","bodegaEIE" )
 
	# prepare a cursor object using cursor() method
	cursor = db.cursor()
 
	# Prepare SQL query to INSERT a record into the database.
	sql = "SELECT * FROM equipo WHERE usuario = '%d'" % (code)
	try:
		# Execute the SQL command
	        cursor.execute(sql)
		# Fetch all the rows in a list of lists.
		results = cursor.fetchall()
		if len(results)  == 0:
			print "ningun equipo registrado al usuario '%d'" %(code)
		else:
			for row in results:
				placa = row[0]
				tipo = row[1]
				marca = row[2]
				modelo = row[3]
				usuario = row[4]
				# Now print fetched result
				print " "
				print "placa=%d,  tipo=%s,  marca=%s,  modelo=%s,  usuario=%s" % (placa, tipo, marca, modelo, usuario)
				print " "
	except:
		print "Error: unable to fecth data"
 
	# disconnect from server
	db.close()

When some returns the equipment, we have to erase his name from the database so we use this module:

#!/usr/bin/python
 
import MySQLdb
 
def cleardb(data):
	# Open database connection
	db = MySQLdb.connect("localhost","user","bodega","bodegaEIE" )
 
	# prepare a cursor object using cursor() method
	cursor = db.cursor()
 
	# Prepare SQL query to UPDATE required records
	sql = "UPDATE equipo SET usuario = NULL WHERE placa = '%s'" %(data)
	try:
	   # Execute the SQL command
	   cursor.execute(sql)
	   # Commit your changes in the database
	   db.commit()
	except:
	   # Rollback in case there is any error
	   db.rollback()
 
	# disconnect from server
	db.close()

We link all the modules in this little script.

#!/usr/bin/python
 
import sys
from time import sleep
from scanner import scan
from update import updatedb
from check import checkdb
from read import readdb
from clear import cleardb
 
 
 
print "digite 'prestar', 'devolver', 'consultar' o 'exit' para lo que desee"
 
exit=False
while not exit:
	a=raw_input()
	if a=="salir":
		exit=True
		print "Bye"
	elif a=="prestar":
		equipo=scan()
		print "el equipo escaneado es '%d'" %(equipo)
		print "dijite ok si es correcto"
		b=raw_input()
		check=checkdb(equipo)
		if ((check == True) and (b == "ok")):
			usuario=scan()
			print "el usuario escaneado es '%d'" %(usuario)		
			dato = (usuario,equipo)
			print "desea prestar al usuario '%d' el equipo '%d'? [si/no] " % dato
			c=raw_input()
			if c=="si":
				updatedb(dato)
				print "equipo prestado"
			elif c=="no":
				print "equipo no prestado, dijite 'scann' para nuevo intento"
		else:
			print "equipo no registrado en la base de datos"
	elif a=="devolver":
		equipo=scan()
		print "el equipo escaneado es '%d'" %(equipo)
		check=checkdb(equipo)
		if check == True:
			print "desea devolver '%d'? [si/no] " % (equipo)
			b=raw_input()
			if b=="si":
				cleardb(equipo)
				print "equipo devuelto"
			elif b=="no":
				print "no se ha devuelto el equipo"
		else:
			print "equipo no registrado en la base de datos"
	elif a=="consultar":
		code=scan()
		readdb(code)
	else:
		print "codigo incorrecto"
		print "digite 'prestar', 'devolver', 'consultar' o 'exit' para lo que desee"
	sleep (1.0)

go back

http://dev.mysql.com/doc/

http://www.mikusa.com/python-mysql-docs/query.html

http://zbar.sourceforge.net/