¿Cómo descargo los datos de algunas tablas SQLite3?

¿Cómo puedo volcar los datos, y solo los datos, no el esquema, de algunas tablas SQLite3 de una database (no todas las tablas)? El volcado debe estar en formatting SQL, ya que debe volver a ingresarse fácilmente en la database más tarde y debe hacerse desde la línea de command. Algo como

sqlite3 db .dump 

pero sin eliminar el esquema y seleccionar qué tablas vaciar.

No dices lo que deseas hacer con el file volcado.

Utilizaría lo siguiente para get un file CSV, que puedo importar a casi todo

 .mode csv -- use '.separator SOME_STRING' for something other than a comma. .headers on .out file.dmp select * from MyTable; 

Si desea reinsert en una database SQLite diferente, entonces:

 .mode insert <target_table_name> .out file.sql select * from MyTable; 

Puedes hacer esto obteniendo la diferencia de los commands .schema y .dump. por ejemplo con grep:

 sqlite3 some.db .schema > schema.sql sqlite3 some.db .dump > dump.sql grep -vx -f schema.sql dump.sql > data.sql 

data.sql file data.sql contendrá solo datos sin esquema, algo como esto:

 BEGIN TRANSACTION; INSERT INTO "table1" VALUES ...; ... INSERT INTO "table2" VALUES ...; ... COMMIT; 

Espero que esto te ayude.

No es la mejor manera, pero en arrendamiento no necesita herramientas externas (excepto grep, que es estándar en los cuadros * nix de todos modos)

 sqlite3 database.db3 .dump | grep '^INSERT INTO "tablename"' 

pero necesitas hacer este command para cada tabla que estés buscando.

Tenga en count que esto no incluye el esquema.

Puede especificar uno o más arguments de tabla para el command especial .dump, por ejemplo, sqlite3 db ".dump 'table1' 'table2'" .

Cualquier respuesta que sugiera usar grep para excluir las líneas CREATE o simplemente tomar las líneas INSERT de la salida sqlite3 $DB .dump fallará gravemente. Los commands CREATE TABLE enumeran una columna por línea (por lo tanto, excluyendo CREATE no la obtendrá todo), y los valores en las líneas INSERT pueden tener líneas nuevas incorporadas (para que no pueda tomar solo las líneas INSERT ).

 for t in $(sqlite3 $DB .tables); do echo -e ".mode insert $t\nselect * from $t;" done | sqlite3 $DB > backup.sql 

Probado en sqlite3 versión 3.6.20.

Si desea excluir ciertas tablas, puede filtrarlas con $(sqlite $DB .tables | grep -v -e one -e two -e three) , o si desea get un subset específico, reemplácelo con one two three .

Como una mejora a la respuesta de Paul Egan, esto se puede lograr de la siguiente manera:

 sqlite3 database.db3 '.dump "table1" "table2"' | grep '^INSERT' 

–o–

 sqlite3 database.db3 '.dump "table1" "table2"' | grep -v '^CREATE' 

La advertencia, por supuesto, es que tienes que tener grep instalado.

En Python o Java o en cualquier lenguaje de alto nivel, el .dump no funciona. Necesitamos codificar manualmente la conversión a CSV. Doy un ejemplo de Python. Otros, se apreciarían ejemplos:

 from os import path import csv def convert_to_csv(directory, db_name): conn = sqlite3.connect(path.join(directory, db_name + '.db')) cursor = conn.cursor() cursor.execute("SELECT name FROM sqlite_master WHERE type='table';") tables = cursor.fetchall() for table in tables: table = table[0] cursor.execute('SELECT * FROM ' + table) column_names = [column_name[0] for column_name in cursor.description] with open(path.join(directory, table + '.csv'), 'w') as csv_file: csv_writer = csv.writer(csv_file) csv_writer.writerow(column_names) while True: try: csv_writer.writerow(cursor.fetchone()) except csv.Error: break 

Si tiene 'datos de panel', en otras palabras, muchas inputs individuales con id agregarán esto a la apariencia y también arrojarán statistics de resumen:

  if 'id' in column_names: with open(path.join(directory, table + '_aggregate.csv'), 'w') as csv_file: csv_writer = csv.writer(csv_file) column_names.remove('id') column_names.remove('round') sum_string = ','.join('sum(%s)' % item for item in column_names) cursor.execute('SELECT round, ' + sum_string +' FROM ' + table + ' GROUP BY round;') csv_writer.writerow(['round'] + column_names) while True: try: csv_writer.writerow(cursor.fetchone()) except csv.Error: break 

De acuerdo con la documentation de SQLite para Command Line Shell para SQLite , puede exportar una tabla SQLite (o parte de una tabla) como CSV, simplemente configurando el "modo" en "csv" y luego ejecutar una consulta para extraer las filas deseadas de la table:

 sqlite> .header on sqlite> .mode csv sqlite> .once c:/work/dataout.csv sqlite> SELECT * FROM tab1; sqlite> .exit 

Luego use el command ".import" para importar datos CSV (valores separados por comas) a una tabla SQLite:

 sqlite> .mode csv sqlite> .import C:/work/dataout.csv tab1 sqlite> .exit 

Lea la documentation adicional sobre los dos casos para tener en count: (1) La tabla "tab1" no existe previamente y (2) la tabla "tab1" ya existe.

Puede usar una herramienta como Administrador de SQLite .

Hay una larga list de tales herramientas aquí .

El mejor método sería tomar el código que haría el volcado sqlite3 db, excluyendo las partes del esquema.

Ejemplo de pseudo código:

 SELECT 'INSERT INTO ' || tableName || ' VALUES( ' || {for each value} ' quote(' || value || ')' (+ commas until final) || ')' FROM 'tableName' ORDER BY rowid DESC 

Ver: src/shell.c:838 (para sqlite-3.5.9) para el código actual

Incluso podría tomar ese caparazón y comentar las partes del esquema y usar eso.

Esta versión funciona bien con nuevas líneas dentro de insertos:

sqlite3 database.sqlite3 .dump | grep -v '^CREATE'

En la práctica, excluye todas las líneas que comienzan con CREATE que es less probable que contengan líneas nuevas

Revisión de otras posibles soluciones

Incluir solo INSERT

 sqlite3 database.db3 .dump | grep '^INSERT INTO "tablename"' 

Fácil de implementar pero fallará si alguna de sus columnas incluye nuevas líneas

Modo de inserción SQLite

 for t in $(sqlite3 $DB .tables); do echo -e ".mode insert $t\nselect * from $t;" done | sqlite3 $DB > backup.sql 

Esta es una solución agradable y personalizable, pero no funciona si tus columnas tienen objects blob como el tipo 'Geometry' en spatialite

Difiere el volcado con el esquema

 sqlite3 some.db .schema > schema.sql sqlite3 some.db .dump > dump.sql grep -v -f schema.sql dump > data.sql 

No estoy seguro por qué, pero no está funcionando para mí

Otra (nueva) solución posible

Probablemente no hay una mejor respuesta a esta pregunta, pero una que me está funcionando es grep las inserciones teniendo en count que hay nuevas líneas en los valores de la columna con una expresión como esta

 grep -Pzo "(?s)^INSERT.*\);[ \t]*$" 

Para seleccionar las tablas, se .dump admite un argumento LIKE para coincidir con los nombres de las tablas, pero si esto no es suficiente, probablemente un script simple sea una mejor opción

 TABLES='table1 table2 table3' echo '' > /tmp/backup.sql for t in $TABLES ; do echo -e ".dump ${t}" | sqlite3 database.db3 | grep -Pzo "(?s)^INSERT.*?\);$" >> /tmp/backup.sql done 

o, algo más elaborado para respetar las keys externas y encapsular todo el volcado en una sola transacción

 TABLES='table1 table2 table3' echo 'BEGIN TRANSACTION;' > /tmp/backup.sql echo '' >> /tmp/backup.sql for t in $TABLES ; do echo -e ".dump ${t}" | sqlite3 $1 | grep -Pzo "(?s)^INSERT.*?\);$" | grep -v -e 'PRAGMA foreign_keys=OFF;' -e 'BEGIN TRANSACTION;' -e 'COMMIT;' >> /tmp/backup.sql done echo '' >> /tmp/backup.sql echo 'COMMIT;' >> /tmp/backup.sql 

Tenga en count que la expresión grep fallará si ); es una cadena presente en cualquiera de las columnas

Para restaurarlo (en una database con las tablas ya creadas)

 sqlite3 -bail database.db3 < /tmp/backup.sql 

La respuesta por retracilación debe ser la más cercana, sin embargo, no funciona para mi caso. Una consulta de inserción simplemente se rompió en el medio y la export simplemente se detuvo. No estoy seguro de cuál es el motivo. Sin embargo, funciona bien durante .dump .

Finalmente escribí una herramienta para dividir el SQL generado desde .dump :

https://github.com/motherapp/sqlite_sql_parser/

Puede hacer una selección en las tablas insertando comas después de cada campo para producir un csv, o usar una herramienta GUI para devolver todos los datos y savelos en un csv.