Tutorial SQlite3 con Python3 en Linux

SQLite es una herramienta que nos permite guardar bases de datos portables para poder usarlas en programas,scripts,etc sin tener que acceder a conexiones de Bases de Datos establecidas en un servidor especifico,en otras palabras(mis palabras) SQLite es como un MySQL portable, SQLite usa una variante no estándar del lenguaje SQL,por lo cual si conoces el lenguaje SQL como con MySQL,no sera dificil comprender el funcionamiento de SQLite.

Primero que nada debemos tener instalado el modulo para Python,en Linux no debe haber problema,he oído que en otros sistemas si,pero en GNU/Linux no,pero de todos modos comprobamos:

python -c 'import sqlite3'

Si no ven ningún error no hay problema,si ven alguno tendrán que instalar,por ejemplo con pip:

pip install sqlite3

Bueno,primero que nada vamos a abrir nuestro editor de texto favorito para comenzar con los ejemplos,pueden usar la consola de python,pero yo recomiendo un editor y la shell de bash(la termina):

O sea dos terminales:

En mi caso vim(desde aquí se puede ejecutar el código sin usar otra terminal,pero yo uso dos).

Primero que nada vamos a crear un archivo .db,si no existe lo crea si no se conecta a el.

Vamos a crear una variable objeto para conectarnos al archivo  que guardara la base de datos:

db = sqlite3.connect('hola.db')

En estos momentos ya creamos un objeto para manipular la base de datos hola.db,ahora lo que tenemos que crear es un cursor para movernos(por así decirlo) dentro de la db,osea ejecutar comandos de lenguaje SQL en la DB:

cur = db.cursor()

Ahora vamos a ejecutar algunos comandos básicos de SQL con la función execute(este tutorial supone que tienes conocimientos básicos de lenguaje SQL):

cur.execute('CREATE TABLE holamundo(pal1 text,pal2 text)')

cur.execute('INSERT INTO holamundo VALUES("Hola","Mundo")')

Con eso ya tendriamos creada una DB basica,ahora vamos a guardar lo que hicimos con la funcion commit de la db:

db.commit()

Siempre debemos ejecutar commit o no guardara los cambios.

Por ultimo vamos a ejecutar la funcion close para que cierre la conexion:

db.close()

Podemos ejecutar nuestro script,asi quedaria:

 #!/usr/bin/env python3  
 import sqlite3  
 db = sqlite3.connect('hola.db')  
 cur = db.cursor()  
 cur.execute('CREATE TABLE holamundo(pal1 text,pal2 text)')  
 cur.execute('INSERT INTO holamundo VALUES("Hola","Mundo")')  
 db.commit()  
 db.close()  

Y listo tenemos una base de datos portable,si no me creen pueden ejecutar la shell de sqlite3 de linux:

sqlite3 hola.db

.tables

SELECT * FROM hola

Bien funciona :D.

Las ordenes que dimos con exec,las podemos dar en una sola linea ejecutando un script interno,con la función executescript del cursor:

Primero vamos a usar comillas triples,que son para varios comentarios,como DATA<< en Perl,Ruby o como --[[ ]]-- en lua:

cur.executescript("""
    CREATE TABLE holamundo(pal1 text,pal2 text);
    INSERT INTO holamundo VALUES("Hola","Mundo");
    INSERT INTO holamundo VALUES("Hola","desde SQLite Python");
""")

Añadí un comando mas para ver como funciona,en este caso no necesitamos hacer un commit ya que agrega y hace un commit internamente en cada orden.

Ya vimos como agregar ahora como obtener:

Para obtener los datos que necesitamos hay varias maneras,primero la mas sencilla,pero menos potente llamar un único valor:

Como se ve arriba el comando execute usa comandos SQL,si usamos comandos de creación crea,pero si usamos comandos de recuperación donde quedan?

Quedan en variables especiales que se convierten en tuplas,en este caso la función fetchone devuelve una tupla del primer valor que coincida:

cur.execute("SELECT * FROM holamundo")
print (cur.fetchone())

Eso nos devuelve una tupla desde una funcion,por lo cual es valido hacer esto:

print (cur.fetchone()[0])

Bien,pero si queremos todo?

Es fácil saberlo (sabiendo ingles básico),con el comando fetchall:

La cual nos devuelve una tupla de dos dimensiones(en este caso),ya que la primera dimensión contiene las dos tuplas que creamos,y la segunda dimensión contiene los valores de cada tupla:

print (cur.fetchall())

Por lo cual es valido hacer esto:

print (cur.fetchall()[1][1])

En este caso nos devuelve esto:

Bien,hay otra forma de recuperación de datos la cual es iterando sobre una orden de ejecución,ya que como nos devuelve una tupla podemos iterar sobre ella:

for tup in cur.execute("SELECT * FROM holamundo"): # Iteramos en la primera dimencion
    for val in tup: # Iteramos en la segunda dimencion
        print (val)
     
Bien vamos a temas mas avanzados de agregación de datos.

Supongamos que queremos poner un dato en la query que ejecuta la API de SQLite,en otras palabras queremos definir los valores que introduciremos en la tabla desde una variable,como le hacemos,¿Podemos usar formateo de cadenas?,Si,por ejemplo:

Borren la DB que creamos desde la shell:

rm hola.db

 #!/usr/bin/env python3  
 import sqlite3  
 db = sqlite3.connect('hola.db')  
 cur = db.cursor()  
 #cur.execute('CREATE TABLE holamundo(pal1 text,pal2 text)')  
 cur.execute("SELECT * FROM holamundo")  
 x,y = "Hola","Mundo" # Creamos variables  
 cur.execute('INSERT INTO holamundo VALUES ("%s","%s")' %(x,y)) # Formateamos  
 db.commit()  
 db.close()  

Eso se puede hacer,pero no se debe hacer,porque?

Por la vulnerabilidad de formateo de strings,por ejemplo una Inyección SQL.

Entonces que hacemos?

Podemos hacerlo de dos maneras creamos un dato tipo diccionario para que la query parse el valor desde los valores de un diccionario que es clave/valor o usando una tupla que no se puede cambiar el valor:

Primer ejemplo, metodo qmark:

cur.execute('INSER INTO holamundo VALUES(?,?)',(x,y))

En este caso usamos un estilo de formateo,pero con el símbolo (?) y le pasamos el valor desde una tupla,la cual no se puede modificar.

Segundo ejemplo, metodo de nombre:

cur.execute('SELECT * FROM holamundo WHERE pal1=:hola',{"hola":"Hola"});

print(cur.fetchall())

Este se ve un poco mas complejo pero es fácil,solo hay que pesarle los valores de un diccionario y como es una string para que no de error de valor debemos anteponer dos puntos en la query SQL,veamos otro ejemplo:

x,y = "Hola", "Mundo" # X vale Hola,Y vale Mundo

dic = {'x':x,'y':y} # Adentro del diccionario X vale X que a la vez vale Hola,Y vale Y que a la vez vale Mundo

cur.execute("INSERT INTO holamundo VALUES(:x,:y)",dic) # Insertamos los valores del diccionario,anteponemos dos puntos para que se reconozca como string.

Ahora como podemos ver las columnas?,ya que podemos ver los valores que contienen pero las columnas?

Aquí falta ver que columnas son las que contienen Hola y Mundo que serian pal1 y pal2,para ello vamos a cambiar el atributo row_factory de la db a sqlite3.Row:

db.row_factory = sqlite3.Row

cur = db.cursor()

cur.execute("SELECT * FROM holamundo")

Ahora podemos acceder a las llaves de la tupla que se crea cuando buscamos algo,recuerdan fetchone:

for i in cur.fetchone().keys():
    print(i)
 
También compatible con len:

print(len(cur.fetchone().keys()))

De esa manera tendremos las columnas de la tabla ya que por defecto fetch nos devuelve una tupla sqlite,y de esta manera nos devuelve una columna sqlite compatible con las funciones keys,len,entre otra.

De hecho la funcion sqlite3.Row es opcional,podemos programar nuestra propia funcion para que sea igual al parametro de row_factory de la DB,es un poco largo de explicar pero veamos un poco:

Creamos una funcion que sea igual a row:_factory,este parametro nos pasa argumentos,asi que vamos a recorrerlos con,con *args(recuerdan el tutorial):

 #!/usr/bin/env python3  
 import sqlite3  
 def datos(*args):  
   for i in args:  
     print (i)  
 db = sqlite3.connect('hola.db')  
 db.row_factory = datos  
 cur = db.cursor()
 cur.execute("select * from holamundo")
 print(cur.fetchone())
 db.close()  

Eso nos dirá los argumentos que nos manda,los cuales son:

El primero es un objeto mas específicamente un cursor,el segundo es la columna que regresa la query que ejecutamos y nos manda un tercero Nulo(None):

Así que podemos hacer esto,para ver las funciones que tienen:

 for i in dir(args[0]):
        print i

for i in dir(args[1]):

        print i

Y nos muestra las funciones de cada uno,en la pagina oficial nos muestra una manera de crear un diccionario,en este ejemplo seria así:

 import sqlite3  
   
 def dict_factory(cursor, row):  
   d = {}  
   for idx, col in enumerate(cursor.description):  
     d[col[0]] = row[idx]  
   return d  
   
 con = sqlite3.connect("hola.db")  
 con.row_factory = dict_factory  
 cur = con.cursor()  
 cur.execute("select * from holamundo")  
   
 print (type(cur.fetchone()))  
   
 for k in cur.fetchone():  
   print (k,"->",cur.fetchone()[k])
   

Todo lo que hicimos lo podemos hacer conectandonos directamente a la memoria ram,el cual nos dara un espacio de trabajo temporal,no lo recomiendo a menos de que sea un trabajo especial como un manejo de memoria basada en un algoritmo temporal,osea como una actualización basada en datos actuales que pueden cambiar,normalmente podemos hacer todo el trabajo en archivos,como en este tutorial en la base hola.db,de todas formas podemos conectarnos a una base de datos creada directamente en la memoria RAM la base de datos se destruirá cuando se cierre la conexión:

Para hacerlo necesitamos usar el parámetro :memory: en la conexión:

  #!/usr/bin/env python3   
  import sqlite3   
  db = sqlite3.connect(':memory:')   
  cur = db.cursor()   
  cur.execute('CREATE TABLE holamundo(pal1 text,pal2 text)')   
  cur.execute('INSERT INTO holamundo VALUES("Hola","Mundo")')  
  cur.execute('SELECT * from holamundo')  
  print(cur.fetchall())   
  db.commit()   
  db.close()  
   

Bien con eso terminamos este tutorial,tal vez haga otra parte ya que falto lo mas avanzado que es crear funciones y clases para usarlas como extensión,pero de momento es todo.

Esto es muy útil cuando se programan juegos,por ejemplo con pygame,o para android,un ejemplo rápido es ponerle un historial de juegos ganados al juego de ahorcado que programe en perl,así es usar Python junto con Perl es sencillo usar lenguajes juntos,así que vamos a ver como quedaría el script y haber si le entienden o encuentran errores con lo que aprendimos en este tutorial:

 #!/usr/bin/ev python3  
   
 from sys import argv  
   
 import sqlite3  
   
 db = sqlite3.connect('records.db')  
 db.row_factory = sqlite3.Row  
 cur = db.cursor()  
 num = 0  
   
 query = "SELECT * FROM ahorcado order by puntuaje"  
   
 try:  
   cur.execute(query)  
 except:  
   cur.execute('CREATE TABLE ahorcado(nombre TEXT,puntuaje INTEGER)')  
   
 def mayor_pal(p):  
   global num  
   if len(p) > num:  
     num = len(p)  
   
 def agregar(x,y):  
   gamer = x  
   puntos = y  
   cur.execute("INSERT INTO ahorcado VALUES(:jugador,:puntuaje)",{"jugador":gamer,"puntuaje":puntos})  
   db.commit()  
   
 def ver(x):  
   for i in x.execute(query):  
     mayor_pal(i[0])   
   print "\tJugador",(" "*num),"Puntuaje\n"  
   for i in x.execute(query):  
     print "\t",i[0],(" "*(num+4)),i[1]  
   
 if len(argv) > 1:  
   if argv[1] == "-a" and len(argv) == 4:  
     agregar(argv[2],argv[3])  
   elif argv[1] == "-v":  
     ver(cur)  
   else:  
     exit()  
   
 db.close()  

Al juego de ahorcado le hice también unas pequeñas modificaciones.

Pueden descargarlo aquí:

Enlace

Esto lo he aprendido de la documentación oficial como siempre,así que pueden darle una leeida para que aprendan de la misma fuente que yo:

http://docs.python.org/2/library/sqlite3.html