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

sqlite pdf

Question

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

sqlite3 db .dump

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

Answer 1

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 objetos 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 cuenta 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 que coincida 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 claves 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 cuenta que la expresión grep fallará si ); es una cadena presente en cualquiera de las columnas

Para restaurarlo (en una base de datos con las tablas ya creadas)

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

Answer 2

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.

Answer 3

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 comandos 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 obtener un subconjunto específico, reemplácelo con one two three .

Answer 4

De acuerdo con la documentación 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 mesa:

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

Luego use el comando ".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 documentación adicional sobre los dos casos para tener en cuenta: (1) La tabla "tab1" no existe previamente y (2) la tabla "tab1" ya existe.

Answer 5

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.

Answer 6

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 entradas individuales con id agregarán esto a la apariencia y también arrojarán estadísticas 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 

Answer 7

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 menos probable que contengan líneas nuevas

Answer 8

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 exportación 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/

Answer 9

No dices lo que deseas hacer con el archivo volcado.

Utilizaría lo siguiente para obtener un archivo 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 reinsertar en una base de datos SQLite diferente, entonces:

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

Answer 10

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 comando para cada tabla que estés buscando.

Tenga en cuenta que esto no incluye el esquema.

Answer 11

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

Answer 12

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 guardarlos en un csv.

Answer 13

Puedes hacer esto obteniendo la diferencia de los comandos .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 archivo 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.