Bases de Datos Relacionales

Conocimiento previo

Una base de datos relacional es una colección de tablas, cada una tien un número fijo de columnas y un número variable de filas. Las columnas tienen un nombre y contienen datos del mismo tipo. Muchos sistemas de bases de datos: comerciales (Oracle, DB2, SQL Server, ...) y libres (MySQL, PostgreSQL, SQLite ...) SQLite está incluida en Python

Usar bases de datos relacionales con Python es muy fácil. Python proporciona un estándar para acceder a bases de datos. La DB API 2.0 es la versión vigente (PEP 249) Módulos compatibles:

• MySQLdb (MySQL)
• psycopg2 (PostgreSQL)
• cx_Oracle (Oracle)
• mxODBC (SQL Server, DB2, Sybase, Oracle, etc.)

Estructura

La DB API usa dos conceptos para realizar los procesos:

• Objeto Conexión
  • conexión con la base de datos
  • Transacciones
• Objeto Cursor
  • Ejecuta las sentencias
  • Accede a los resultados

Conexión

• El objeto conexión se encarga de conectar con la base de datos
• Proporciona acceso (red/RPC) a la base de datos.
• Este objeto no permite lanzar sentencias.
• Gestiona las transacciones (grupos lógicos de sentencias)

Cursor

• Creado a partir de una conexión
• Sentencias de manipulación y de consulta en la bbdd.
• Método execute(), que acepta una secuencia de parámetros.
• Almacena los datos del result set depués de lanzar la consulta.
• Método fetch*() que lee los datos del result set

Transacciones

• DB API 2.0 soporta transacciones (si el motor las soporta) desde el objeto conexión.
• conexión: commit / rollback

Introspección del esquema

• Busca el tipo de las columnas de una tabla:
  • Método sencillo:

cursor.execute(‘select * from testtable where 1=0’)
# mira el atributo cursor.description

• • Método avanzado:

cursor.columns(table='testtable')
rows = cursor.fetchall()

Muy importante: Paso de parámetros

• No hay ue hacer nunca sustitución de cadenas de caracteres para evitar inyección de código.
• Hay una variable paramstyle que define cómo se pasan los parámetros.
• Todos los módulos admiten al menos uno de:
  • 'qmark': Signo de interrogación, ej. '...WHERE name=?'
  • 'numeric': Numerico, posicional, ej. '...WHERE name=:1'
  • 'named': por Nombre, ej. '...WHERE name=:name'
  • 'format': Formato ANSI C, ej. '...WHERE name=%s'
  • 'pyformat': Formato Python, ej. '...WHERE name=%(name)s'

Ejemplo con sqlite

# Fuente: http://mundogeek.net/archivos/2008/06/25/bases-de-datos-en-python
import sqlite3 as dbapi
 
# 1. Creamos objeto conexión
bbdd = dbapi.connect("bbdd.dat")
 
# 2. Creamos un cursor
cursor = bbdd.cursor()
 
# 3. Usamos cursor para acceder a la  base de datos
# 3.1. create
cursor.execute("""create table empleados (dni text,
                  nombre text,
                  departamento text)""")
 
# 3.2. insert
cursor.execute("""insert into empleados
                  values ('12345678-A', 'Manuel Gil', 'Contabilidad')""")
 
bbdd.commit()
 
# 3.3 select
cursor.execute("""select * from empleados
                  where departamento='Contabilidad'""")
 
# extraer resultados de select --> están almacenados en cursor
for tupla in cursor.fetchall():
    print tupla

Ejemplo mysql

import MySQLdb
 
dbusername = "user" 
dbname = 'user_private' 
dbpassword = 'some_password'
 
# connect to the database 
db = MySQLdb.Connect(db = dbname, user = dbusername, passwd = dbpassword)
 
#To perform a query, you first need a cursor, and then you can execute queries on it. 
cursor = db.cursor()
 
# create the query 
query = "SELECT * FROM foo"
 
# execute the query 
cursor.execute(query)
 
# retrieve the result 
results = cursor.fetchall()
 
for firstname, age, city in results: 
    print firstname, age, city

Ampliación MySQL

• Acceso a MySQL con Python

Acceso a Oracle

• http://st-curriculum.oracle.com/obe/db/11g/r2/prod/appdev/opensrclang/python/python.htm
• http://www.orafaq.com/wiki/Python
• http://cx-oracle.sourceforge.net/

Configuración

set ORACLE_HOME if this environment variable doesn't exist (see this Oracle FAQ)
python can find oraocci11.dll for Oracle 11g, oraocci10.dll for Oracle 10g 
  or oraclient9.dll for Oracle 9i by adding the folder (should be %ORACLE_HOME%/bin) 
  where the DLL is in you PATH environment variable
use the correct binary package (i.e. built for the right Python/Oracle versions)

Ejemplo con cx_Oracle

import cx_Oracle
conn_str='scott/tiger@192.168.1.90:1521/DESARROLLO'
db_conn = cx_Oracle.connect(conn_str)
cursor = db_conn.cursor()
cursor.execute('SELECT username, user_id, created FROM dba_users')
 
registros = cursor.fetchall()
for r in registros:
    print r
 
('MGMT_VIEW', 46, datetime.datetime(2008, 10, 15, 13, 4, 47))
('SYS', 0, datetime.datetime(2008, 10, 15, 12, 43, 8))
('SYSTEM', 5, datetime.datetime(2008, 10, 15, 12, 43, 8))
('DBSNMP', 24, datetime.datetime(2008, 10, 15, 12, 51, 3))
('SYSMAN', 44, datetime.datetime(2008, 10, 15, 13, 3, 24))

Ejemplo con zxJDBC (jython)

from com.ziclix.python.sql import zxJDBC
d, u, p, v = "dbc:oracle:thin:@172.30.6.190:1521/enlaces5", 'dai1', 'tiger', "oracle.jdbc.driver.OracleDriver"
db = zxJDBC.connect(d, u, p, v)
cursor = db.cursor()
 
cursor.execute("SELECT banner FROM sys.v_$version")
 
for l in cursor.fetchall():
    print l
 
cursor.execute("SELECT * from emp")
 
for l in cursor.fetchall():
    print l
 
 
print db.dbname
print db.dbversion
print cursor.updatecount
print zxJDBC.paramstyle

Inyección SQL :: Seguridad

• http://www.theprojectxblog.net/manual-mysql-injection-noobz-guide/