11 - Curso de Python - Bases de datos

Cualquier duda suelo atender en este foro de python

Todo el código de este curso de Python gratuito está en github https://github.com/chuidiang/chuidiang-ejemplos/tree/master/PYTHON/curso-python. Puedes usar línea comandos python para ir siguiendo los ejemplos.

Anterior: 10 - Curso de Python - Datetime -- Índice: Curso de Python -- Siguiente: 12 - Curso de Python - Try Exception

API base de datos en python

Python especifica una serie de reglas (API) para conexión con las bases de datos con la intención de que la forma de tratar con cualquier base de datos sea, hasta donde sea posible, igual para todas las bases de datos: MySQL, MariaDB, PostgreSQL, etc. Esta API está definida en PEP249. Python viene con una base de detos embebebidda, SQLite, que es la que usaremos en los ejemplos. Al venir preinstalada con Python, puedes seguir los ejemplos sin necesidad de instalar y configurar otras bases de datos como PostgreSQL o MySQL.

Los módulos python para tratar con una base de datos concreta deberían cumplir esta API. Veamos qué pide la API en términos generales. Iremos haciendo los ejemplos con SQLite. su documentación pone que cumple con PEP249. Creo que es discutible, pero por simplicidad y para que no tengas que instalarte una base de datos, usaremos SQLite.

Interface de los módulos python para base de datos

Vamos primero a ver con ejemplos sencillos los pasos para establecer conexión a base de datos y ejecutar sentencias SQL. En un segundo bloque, veremos más posiblidades de las funciones y objetos que se van explicando.

Objetos conexión

La API especifica que los módulos deben tener un objeto Connection que represente la conexión a la base de datos. Estas conexiones deben obtenerse a través de un método connect() que puede admitir los parámetros que necesite para la conexión a su base de datos concreta. En el caso de SQLlite embebido, lo obtenemos de esta forma

>>> import sqlite3
>>> con = sqlite3.connect('example.db')

Los parámetros que recomienda la API

Parametro	Significado
dsn	Nombre de la fuente de datos como String (data source name)
user	Nombre de usuario (opcional)
password	Contraseña (opcional)
host	Nombre del host que alberga el motor de base de datos (opcional)
database	Nombre de la base de datos (opcional)

Aquí hay una posible discrepancia de SQLlite con PEP249. El parámetro obligatorio según esa tabla es dsn mientras que database es opcional. El parámetro 'example.db' que hemos pasado en el ejemplo es justamente database y no dsn. Además, en SQLite es obligatorio database.

>>> con = sqlite3.connect(dsn='example.db')
Traceback (most recent call last):
  File "<stdin>", line 1, in <module>
TypeError: function missing required argument 'database' (pos 1)

Los objetos Connection deben tener los siguientes métodos:

• close() para cerrar la conexión cuando ya no la necesitemos más.
• commit() y rollback(). Hay ocasiones que necesitamos hacer varias operaciones en base de datos y necesitamos que se hagan o bien todas o bien ninguna. Si cualquiera de ellas fallara, necesitaríamos que se deshicieran todas las que llevamos hechas hasta ese momento. Estas dos funciones nos ayudan a ello. Vamos haciendo cada una de nuestras operaciones de forma que la base de datos no las haga realmente hasta que le digamos. Si todas tienen éxito, llamamos a commit() para indicarle a base de datos que ya hemos terminado nuestras transacciones y que las haga persistentes definitivamente. Si por el contrario, durante el proceso cualquiera de ellas falla, llamamos a rollback() y la base de datos deshará todos los cambios.
• cursor(). Para obtener una instancia de Cursor que es la clase que nos ayudará a hacer transacciones concretas. Es la que vemos a continuación.

Cursores

Obtenemos un Cursor con una simple llamada al método cursor()

>>> cur = con.cursor()

A partir del objeto Cusor, podemos empezar a ejecutar sentencias SQL con la base de datos. Vamos a crear una tabla y hacer un INSERT en ella.

>>> cur.execute('''CREATE TABLE stocks
...                (date text, trans text, symbol text, qty real, price real)''')
<sqlite3.Cursor object at 0x0000028B071DD140>
>>> cur.execute("INSERT INTO stocks VALUES ('2006-01-05','BUY','RHAT',100,35.14)")
<sqlite3.Cursor object at 0x0000028B071DD140>
>>> con.commit()
>>> con.close()

Hemos creado una tabla de acciones de bolsa (stocks) con los campos fecha (date), transacción (trans), simbolo de la acción (symbol), cantidad (qty) y precio (price). Luego hemos hecho una inserción para una compra (BUY) de acciones de Red Hat (RHAT). 100 acciones a un precio de 35.14. No debemos olvidarnos ejecutar al final commit() para que los datos se guarden definitivamente en base de datos. Y de llamar a close() cuando no necesitemos más la conexión.

Vamos ahora a hacer la consulta de lo que acabamos de insertar. Empezamos desde cero, con el intérprete de comandos de python recién abierto.

>>> import sqlite3
>>> con = sqlite3.connect('example.db')
>>> cur = con.cursor()
>>> cur.execute('select * from stocks')
<sqlite3.Cursor object at 0x000002E2EC15D140>
>>> cur.fetchone()
('2006-01-05', 'BUY', 'RHAT', 100.0, 35.14)

Importamos de nuevo el módulo sqlite3, establecemos de nuevo la conexión con la base de datos y obtenemos el cursor. Una vez tenemos el cursor, ejecutamos la sentencia select * from stocks para obtener el listado de compra/venta de acciones.

La función fetchone() nos devuelve una tupla con uno de los resultados de la consulta, la siguiente disponible. Tenemos varias funciones de este estilo para recoger los resultados de la consulta:

• fetchone()
• fetchmany(size)
• fetchall()

Todos ellos van leyendo los resultados de la consulta. fecthone() de uno en uno. fecthmany() de N en N, según le digamos en el parámetro. Si no ponemos parámetro, traerá tantos como diga cur.arraysize, que por defecto es 1. fetchall(). En cualquier caso, si el número de resultados no es conocido y puede ser grande, conviene traérselos de uno en uno o en bloques de un tamaño adecuado.

Vamos a repetir el ejemplo, pero haciendo varias inserciones para ver cómo traeríamos todos los resultados con un bucle. Nuevamente suponemos que partimos de dero del interprete de comandos de python, pero con la tabla ya creada.

>>> import sqlite3
>>> con = sqlite3.connect('example.db')
>>> cur = con.cursor()

>>> # Hacemos tres inserciones de datos
>>> cur.execute("INSERT INTO stocks VALUES ('2006-01-05','BUY','RHAT',100,35.14)")
<sqlite3.Cursor object at 0x0000028B071DD140>
>>> cur.execute("INSERT INTO stocks VALUES ('2006-01-06','SELL','KO',50,63.16)")
<sqlite3.Cursor object at 0x000002E2EC15D140>
>>> cur.execute("INSERT INTO stocks VALUES ('2006-01-07','BUY','JNJ',150,170.24)")
<sqlite3.Cursor object at 0x000002E2EC15D140>

>>> # Que no se nos olvide el commit()
>>> con.commit()

>>> # Hacemos la consulta
>>> cur.execute('select * from stocks')
<sqlite3.Cursor object at 0x000002E2EC15D140>

>>> # Obtenemos el primer resultado. Bucle mientras no sea None. Sacamos el resultado por pantalla
>>> # y pasamos al siguiente.
>>> res = cur.fetchone()
>>> while (res != None):
...    print(res)
...    res = cur.fetchone()
...
('2006-01-05', 'BUY', 'RHAT', 100.0, 35.14)
('2006-01-06', 'SELL', 'KO', 50.0, 63.16)
('2006-01-07', 'BUY', 'JNJ', 150.0, 170.24)

>>> # Cierre
>>> con.close()

Sobre el bucle. Cogemos el primer resultado con cur.fetchone() y lo almacenamos en la variable res. A partir de ahí, un bucle mientras res no sea None. Si no hubiera resultados, nunca entramos en el bucle. Dentro del bucle hacemos lo que queramos hacer con el resultado, en este caso, sacarlo por pantalla. Y como última línea del bucle, obtenemos el siguiente resultado. Si es None, el bucle terminará.

Si obtenemos todos los resultados de golpe, fecthall() nos devuelve una colección que podemos iterar.

>>> cur.execute('select * from stocks')
<sqlite3.Cursor object at 0x000002E2EC15D140>
>>> res = cur.fetchall()
>>> for transaction in res:
...    print(transaction)
...
('2006-01-05', 'BUY', 'RHAT', 100.0, 35.14)
('2006-01-06', 'SELL', 'KO', 50.0, 63.16)
('2006-01-07', 'BUY', 'JNJ', 150.0, 170.24)

Llamamos a fetchall() y guardamos el resultado en res. El resultado es una lista, que puede tener cero elementos si la consulta no da resultados. Así que solo nos queda hacer un bucle para recorrer dicha lista.

Más funciones de base de datos

Hemos dado un repaso a lo fundamental: conexión con la base de datos, obtener un cursor para ejecutar sentencias SQL y leer los resultados. Pero lo hemos hecho de forma directa, usando las funciones de la forma más simple. Python sin embargo ofrece más posibilidades que vamos a ver en este apartado.

Parámetros en SQL

Suele ser habitual que en la sentencia SQL queramos meter valores concretos que están en variables y no escribirlos directamente en el string de la sentencia SQL. Hay varias posibilidades, veremos aquí dos de ellas. Imagina que queremos hacer una consulta para ver las acciones de un determinado símbolo que se han comprado o vendido. Tenemos estas dos posibilidades:

>>> cur.execute('select * from stocks where symbol=?',['KO'])
<sqlite3.Cursor object at 0x000002E2EC15D140>
>>> cur.fetchone()
('2006-01-06', 'SELL', 'KO', 50.0, 63.16)

En esta opción ponemos ? en el lugar donde irían los valores. Al llamar a cur.execute() pasamos en un primer parámetro la SQL, pero pasamos también una lista (o tupla) como segundo parámetro con los valores concretos que queremos para esos ?. En el ejemplo solo es uno, así que pasamos una lista de un elemento con el valor del símbolo de la acción. Es una lista porque la hemos puesto entre corchetes []. Si queresmos tupla, sería entre paréntesis (), pero recuerda que para un solo elemento debemos añadir una coma al final, así ('KO',).

Veamos una segunda opción

>>> cur.execute('select * from stocks where symbol=:symbol',{'symbol':'KO'})
<sqlite3.Cursor object at 0x000002E2EC15D140>
>>> cur.fetchone()
('2006-01-06', 'SELL', 'KO', 50.0, 63.16)

Aquí en vez de interrogante, ponemos un nombre precedido de dos puntos :symbolo. Como segundo parámetro podemos pasar un diccionario con claves (nombres) y valores. La clave (nombre) debe coincidir con el nombre que hemos puesto en la sentencia SQL sin los dos puntos.

Conversión tipos Python a SQL

Si te has fijado, cuando hemos puesto interrogantes en la SQL, no nos hemos preocupado de tipos, de poner comillas simples cuando el tipo es string, por ejemplo. El módulo SQLite3 sabe interpretar los tipos de Python y ponerlos de acuerdo a los tipos que tienen en las columnas de la tabla. Las convesions que hace son:

Tipo SQLite	Tipo Python
NULL	None
INTEGER	int
REAL	float
TEXT	str by default. Podría cambiarse para que devuelva bytes
BLOB	bytes

La conversión es en ambos sentidos. Los tipos Python son los que obtenemos cuando hacemos consultas o los que tenemos que pasar para hacer insert/update en las tablas.

Para fechas y horas se usa una cadena de texto en el formato que entienda la base de datos.

Ejecución de varias sentencias

Cursor tiene un método executemany(). Le pasamos una SQL con interrogantes y luego le pasamos una lista de listas. Veamos el ejemplo con un insert

>>> datos = [
...    ['2006-01-06', 'SELL', 'AAPL', 50.0, 161.16],
...    ['2006-01-06', 'SELL', 'PEP', 50.0, 176.16]]
>>> cur.executemany('insert into stocks values (?,?,?,?,?)',datos)
>>> con.commit()

En datos hemos metido una lista con dos elementos. Cada elemento es una nueva lista con los datos de una nueva transacción de acciones que queremos registrar en base de datos. Son ventas de Apple y de Pepsi. Luego llamamos a cur.executemany(). Como primer parámetro pasamos la sentencia SQL de insert poniendo los valores concretos a insertar como interrogantes. Como segunda parámetro pasamos la lista datos con dos elementos. Como la lista tiene dos elementos, la sentencia SQL se ejecutará dos veces. En cada ejecución se reemplazarán los interrogantes por los valores concretos de cada elemento de la lista. Es decir, la primera ejecución usara los valores de la acción de Apple (AAPL). La segunda ejecución los valores de la acción de Pepsi (PEP). El resultado, dos inserciones en base de datos. Y no te olvides del commit().

También tenemos executescript() al que podemos pasar, en un solo string, varias sentencias sql separadas por punto y coma ;. Esta puede ser útil para la creación de tablas, triggers, inserción de datos fijos en el inicio, etc.

>>> cur.executescript("""
...     begin;
...     create table person(firstname, lastname, age);
...     create table book(title, author, published);
...     create table publisher(name, address);
...     commit;
... """)

Aquí hemos creado tres tablas. El begin; y commit; son para empezar una transacción que consta de varias sentencias y queremos que se ejecuten todas o ninguna. Con begin; comentamos la transacción que consiste en crear tres tablas. Y una vez hecho, ejecutamos commit; para que se creen definitivamente en base de datos.

Row

sqlite3 nos permite obenter los resultados de las consultas en una clase Row en vez de en una tupla normal. Esto no forma parte de la API DB de python. Es usual que los módulos de las bases de datos ofrezcan una alternativa mejorada a obtener una simple tupla para un resultado o lista de tuplas cuando hay varios. Row es específico de SQLite, así que lo aquí contado sólo vale para SQLite.

La conexión con obtenida al conectarnos a base de datos tiene un atributo row_factory que podemos usar para decir como queremos los resultados. Aquí podemos asignar cualquier función que admita dos parámetros: un cursor y una lista de resultados. La función debe devolver "algo" que tenga los resultados y que presumiblemente sea más cómodo o dé más posibilidades que una simple tupla. Por ejemplo, puede devolver un diccionario donde las claves son los nombres de las columnas de resultados y los valores los valores de los mismos. De esta forma, en vez de acceder por índice a las columnas de resultaods, podemos acceder por el nombre de la columna, que siempre es más legible.

No vamos a ver aquí un ejemplo de como hacerlo. Baste decir que Row cumple esto y podemos por tanto asignarla a row_factory

>>> con = sqlite3.connect('example.db')
>>> con.row_factory = sqlite3.Row
>>> cur = con.cursor()
>>> cur.execute('select * from stocks')
>>> result = cur.fetchone()
>>> type(result)
<class 'sqlite3.Row'>

En la segunda línea, después de establecer la conexión, rellenamos el row_factory. Luego hacemos una consulta, llamamos a fetchone() para obtener el primer resultado y vemos de qué tipo es. Como era de esperar, no es una tupla, es de tipo Row. Y ahora a disfrutar de los resultados.

>>> result.keys()
['date', 'trans', 'symbol', 'qty', 'price']

el método keys() nos da una lista de los nombres de las columnas.

>>> for i in result.keys():
...    print (i, ' : ' ,result[i])
...
date  :  2006-01-05
trans  :  BUY
symbol  :  RHAT
qty  :  100.0
price  :  35.14

Esto nos facilita hacer bucles, acceder a los resultados y sacar los nombres de las columnas.

Otras bases de datos en Python: MySQL, PostgreSQL

Todo lo comentado aquí es para SQLite, que viene embebida con Python. Para otras bases de datos como MySQL, MariaDB o PostgreSQL depende de hasta qué punto el que ha desarrollado el módulo siga la espcificación de la "Python Database API Specification PEP 249". Muchas cosas serán comunes, otras serán particulares o mejoras de la especificación.

En cualquier caso, tienes que instalarte el módulo correspondiente, desde una ventana de comandos de windows o terminal de linux, podemos instalar el módulo de MySQL. La primera opción instala la última versión disponible. La segunda una versión concreta.

C:\>pip install mysql-connector-python
C:\>pip install mysql-connector-python==8.0.11

Para PostgreSQL hay un par de módulos, puedes instalar el que más te guste, sólo es necesario uno.

C:\>pip install psycopg2
C:\>pip install pygresql

Anterior: 10 - Curso de Python - Datetime -- Índice: Curso de Python -- Siguiente: 12 - Curso de Python - Try Exception