Etiqueta: SQLite

Conexión con base de datos SQLite en el lenguaje R

en por Donnie Rocken Lenguajes de programación, SQLDeja un comentario

El lenguaje de programación R es tremendamente popular entre los matemáticos por estar desarrollado enfocado al análisis estadístico y por ser software libre (licencia GNU/GPL). Y siendo un lenguaje orientado a la estadística la lógica nos dicta que tiene que ser posible conectar con una base de datos.

Por suerte se han desarrollado librerías que sirven como interfaz para trabajar con la mayoría de los sistemas gestores de bases de datos más populares del mercado: SQL-Server, MySQL, PosgreSQL, Oracle y el caso que hoy nos ocupa, SQLite. Principalmente dispones de tres librerías para estas conexiones: ODBC (un standar desarrollado por Microsoft), DBI (basado en DBI de Perl y adaptado de forma nativa para R) y dplyr (otra aproximación nativa profundamente integrada). En este artículo vamos a ver cómo hacerlo con DBI, puesto que es la solución que me han recomendado más programadores de R veteranos.

El primer paso, claro, será instalar los paquetes de la librerías necesarias:

install.packages(c("DBI", "RSQLite"))

Y una vez instalados lo siguiente simplemente es añadir las librerías a nuestro código y configurar una conexión:

library(DBI)
library(RSQLite)

# Definimos el driver
driver <- dbDriver("SQLite")

#realizamos la conexión con dbConnect()
#Esta función recibe como primer parámetro el driver o un objeto
#de conexión ya existente, y como segundo parámetro todos los
#parámetros de conexión que requiere nuestra base de datos.
#SQLite solo necesita la ruta al fichero de la base de datos
#Otros SGBD necesitarán más
archivo_sqlite <- system.file("home/database.sqlite")

conexion <- dbConnect(driver, archivo_sqlite)

#Pdemos ejecutar consultas con dbSendQuery()
#la función recibe el objeto conexión y una consulta SQL.
resultado <- dbSendQuery(conexion, "SELECT * FROM baseDatosEjemplo")

#Para cerrar la conexión con la base de datos
dbDisconnect(conexion)

Crear y borrar una vista en SQLite

en por Donnie Rocken Informática, SQLDeja un comentario

¿Qué es una vista?

Una vista es una consulta cuyo resultado se presenta como una tabla, puesto que tienen la misma estructura: filas y columnas. La diferencia es que sólo se almacena de ellas la definición y no los datos, que se extraen de las otras tablas de la base de datos. En SQLite la vistas son de solo lectura, de forma que no se pueden ejecutar instrucciones INSERT, DELETE o UPDATE sobre ellas para actualizar las tablas.

¿Cómo creo una vista en SQLite?

Para crear una vista en SQLite tenemos la instrucción CREATE VIEW. Tendremos que asignar un nombre a la vista que creamos y ejecutar una consulta que nos devuelva los datos que formarán nuestra vista. La sintaxis sería:

CREATE VIEW Vista_Nueva 
AS 
SELECT
	t1.Id,
        t1.Campo1,
        t2.Campo1 as Campo2
FROM
	tabla t1
INNER JOIN tabla2 t2 ON t1.IdEnTabla2 = t2.Id;

Vamos a poner como ejemplo que tenemos una tabla con variedades de quesos, otra con países y otra con tipos de leche. Y queremos una vista que nos muestre de qué país y con qué tipo de leche está hecho cada queso. Pues podríamos sacarlo con una consulta así:

CREATE VIEW v_QuesosDetallada 
AS 
SELECT
	q.Id,
        q.Nombre,
        p.Nombre as Pais,
        l.Descripcion as TipoLeche
FROM
	Quesos q
INNER JOIN paises p ON q.IdPais = p.Id
INNER JOIN leches l ON q.IdTipoLeche = l.Id;

¿Cómo borro una vista?

Para eliminar una vista en SQLite tenemos la instruccion DROP VIEW, que iría seguida del nombre de la tabla. Siguiendo con el ejemplo de arriba, para borrar la vista v_QuesosDetallada tendríamos que ejecutar la siguiente instrucción:

DROP VIEW v_QuesosDetallada;

De esta forma eliminaríamos la vista de nuestra base de datos.

Si intentamos crear una vista con un nombre que ya existe o si intentamos borrar una que no existe recibiremos un mensaje de error. Por tanto es interesante asegurarse utilizando IF NOT EXISTS a la hora de crear e IF EXISTS a la de borrar.

Calcular la edad a partir de una fecha en SQLite.

en por Donnie Rocken Informática, SQLDeja un comentario

Otra entrada sobre SQLite, vendrán algunas más ya que he tenido que estudiar un poco sobre ello para utilizarlo en una aplicación. Y ya que nos ponemos con consultas y truquitos vamos con el siguiente: ¿Cómo calculamos la edad a partir de una fecha? Ya lo vimos en el pasado con SQL-Server, con MySQL y con PostgreSQL, incluso fuera del tema de las bases de datos también lo hicimos con Javascript.

Supongamos que tenemos una base de datos Usuarios con un campo FechaNacimiento donde almacenamos, como te puedes imaginar, una fecha. Para calcular la edad a día de hoy podemos hacer dos cosas:

La primera es el clásico «Restamos un año a otro y luego si el día del cumpleaños todavía no llegó restamos uno año más«, que podríamos expresar así en una consulta:

SELECT (strftime('%Y', 'now') - strftime('%Y', FechaNacimiento )) - (strftime('%m-%d', 'now') < strftime('%m-%d', FechaNacimiento )) from Usuarios;

Como ves, en primer lugar extraemos los años de las dos fechas y los restamos. En la segunda resta añadimos la compración entre mes y día actual y mes y día de la fecha, dicha evaluación devolverá 1 si es anterior y 0 si no lo es, de esta forma resta 1 en ese caso.

Otra solución, convertir ambas fechas a un formato numérico y restarlas, y después convertir a un formato de número entero:

Select cast(strftime('%Y.%m%d', 'now') - strftime('%Y.%m%d', FechaNacimiento ) as int) from Usuarios;

Utilizar SQLiteStudio en Ubuntu

en por Donnie Rocken Informática, Software, SQLDeja un comentario

SQLiteStudio es un software libre multiplataforma que nos proporciona una interfaz gráfica para trabajar con bases de datos SQLite. Existen versiones para Linux, Windows y MacOS.

En el siguiente enlace puedes entrar a la página de descargas de SQLiteStudio, donde hay dos opciones: un instalador y un paquete .tar.xz con una versión portable del programa.

Durante las pruebas he tenido problemas con la versión con instalador en una máquina con Xunbutu, pero la versión portable ha funcionado sin problemas. Basta con descargarla y desempaquetarla:

SQLite Studio Descarga

Descomprimir SQLitestudio

Una vez desempaquetado buscamos el programa y lo ejecutamos. En la primera ejecución nos pedirá que definamos el idioma:

Ejecutable sqlitestudio

Y listo, ya está funcionando SQLiteStudio en tu equipo:

SQLite Xubuntu

Importar datos desde un fichero CSV a una tabla de SQLite

en por Donnie Rocken Informática, SQLDeja un comentario

Ya hemos hablado en el pasado de ficheros CSV en este blog, y hoy vuelven a la primera línea.

En este caso porque ayer estaba aconsejando a un amigo sobre cómo agilizar una tarea de inserción de datos en SQLite y llegamos a la conclusión de que, teniendo los datos en un fichero CSV tenía que haber una forma de importarlo. El caso es que San Google nos dio la respuesta, pero me fijé en que no había mucha cosa válida sobre el tema en español. Total, que me he decidido a traducir la entrada que nos fue más útil, que fue esta de HackGeo. Bueno, como siempre no será una traducción literal, sino una adaptación/explicación de su solución.

En fin, como dice el artículo original, hay una serie de puntos a considerar antes de ponerse a trabajar:

  • SQLite instalado en tu sistema operativo.
  • Una base de datos ya creada (si quieres seguir el ejemplo del original, podéis descargar ejemplos de código desde allí)
  • Una tabla creada en esa base de datos cuyos campos y tipos coincidan con los datos de nuestro CSV, tal que así: 
    CREATE TABLE counties (
    name        CHAR,
    state       CHAR,
    state_abbrv CHAR( 2 ),
    FIPS        CHAR( 5 )
);
  • Un fichero CSV cuyo esquema coincida con vuestra tabla, como por ejemplo:

    Maui,Hawaii,HI,15009
    Kauai,Hawaii,HI,15007
    Kalawao,Hawaii,HI,15005
    Honolulu,Hawaii,HI,15003
    Hawaii,Hawaii,HI,15001

Ahora, siguiendo el ejemplo original, ya tenemos todos los datos y el software listos. Ya sólo quedan cuatro pasos para tener todo listo:

  1. Lanza un terminal
  2. Abre la herramienta de terminal de comandos de SQLite, escribiendo sqlite3 y pasándole como parámetros la ruta absoluta a vuestra base de datos: 
    sqlite3 '/Path/To/HackGeo - SQLite CSV Import Example/HackGeo.db'
  3. Pon la coma como separador por defecto (si usas tu propio CSV y no el del ejemplo entonces define el separador que corresponda, en caso de que no sea la coma): 
    .separator ','
  4. Ahora ejecuta el importador de SQLite para volcar los datos del CSV en tu equipo. Le tenemos que pasar dos parámetros a este comando: la ruta del csv y el nombre de la tabla donde insertaremos los valores: 
    .import '/Path/To/HackGeo - SQLite CSV Import Example/Counties.csv' counties

Y sólo con esto ya tenemos los datos pasados del CSV a la base de datos SQLite. Ya podéis cerrar el terminal de línea de comandos y empezar a trabajar con vuestros nuevos datos. Y si sabéis inglés en HackGeo tenéis muchos artículos interesantes sobre desarrollo, sistemas, etc…