Вопрос:

Как вывести данные некоторых таблиц SQLite3?

sql sqlite

195507 просмотра

13 ответа

105042 Репутация автора

Как вывести данные, и только данные, а не схему, некоторых таблиц SQLite3 базы данных (не всех таблиц)? Дамп должен быть в формате SQL, так как он должен быть легко повторно введен в базу данных позже и должен быть выполнен из командной строки. Что-то вроде

sqlite3 db .dump

но без выгрузки схемы и выбора таблиц для выгрузки.

Автор: pupeno Источник Размещён: 16.09.2008 06:52

Ответы (13)


-3 плюса

0 Репутация автора

Вы можете сделать выбор в таблицах, вставляя запятые после каждого поля, чтобы создать CSV, или использовать инструмент с графическим интерфейсом, чтобы вернуть все данные и сохранить их в CSV.

Автор: unicorn.ninja Размещён: 16.09.2008 06:55

2 плюса

538 Репутация автора

Лучший способ - взять код, который будет делать дамп sqlite3 db, за исключением частей схемы.

Пример псевдокода:

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

Смотрите: src/shell.c:838 (для sqlite-3.5.9) для актуального кода

Вы можете даже взять эту оболочку, закомментировать части схемы и использовать ее.

Автор: harningt Размещён: 17.09.2008 02:05

203 плюса

3400 Репутация автора

Вы не говорите, что вы хотите сделать с сохраненным файлом.

Я бы использовал следующее, чтобы получить файл CSV, который я могу импортировать практически во все

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

Если вы хотите переустановить в другую базу данных SQLite, то:

.mode insert <target_table_name>
.out file.sql 
select * from MyTable;
Автор: CyberFonic Размещён: 13.10.2008 10:18

36 плюса

5932 Репутация автора

Не самый лучший способ, но в аренду не нужны внешние инструменты (кроме grep, который в любом случае является стандартным для * nix)

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

но вам нужно выполнить эту команду для каждой таблицы, которую вы ищете.

Обратите внимание, что это не включает схему.

Автор: polyglot Размещён: 08.01.2009 01:04

33 плюса

301 Репутация автора

Вы можете указать один или несколько табличных аргументов для специальной команды .dump, например sqlite3 db ".dump 'table1' 'table2'".

Автор: Paul Egan Размещён: 21.12.2009 06:12

139 плюса

427 Репутация автора

Вы можете сделать это, получая разницу команд .schema и .dump. например с помощью grep:

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

data.sql файл будет содержать только данные без схемы, примерно так:

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

Я надеюсь, это поможет вам.

Автор: jellyfish Размещён: 23.09.2011 08:10

8 плюса

3311 Репутация автора

В качестве улучшения ответа Пола Игана это можно сделать следующим образом:

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

--или же--

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

Предостережение, конечно, в том, что у вас должен быть установлен grep.

Автор: Drew Размещён: 01.11.2011 11:19

2 плюса

928 Репутация автора

Эта версия хорошо работает с символами новой строки внутри вставок:

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

На практике исключаются все строки, начинающиеся с CREATEкоторых с меньшей вероятностью содержат символы новой строки

Автор: Elia Schito Размещён: 16.05.2012 01:40

9 плюса

9553 Репутация автора

Любой ответ, который предлагает использовать grep, чтобы исключить CREATEстроки или просто захватить INSERTстроки из sqlite3 $DB .dumpвывода, потерпит неудачу. В CREATE TABLEсписок команд один столбец в каждой строке (так , исключая CREATEне получите все это), и значения на INSERTлинии могут иметь встроенные символы новой строки (так что вы не можете получить только те INSERTстроки).

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

Протестировано на sqlite3 версии 3.6.20.

Если вы хотите исключить определенные таблицы, вы можете отфильтровать их $(sqlite $DB .tables | grep -v -e one -e two -e three), или если вы хотите получить определенное подмножество, замените его на one two three.

Автор: retracile Размещён: 16.11.2013 03:18

6 плюса

8998 Репутация автора

В Python или Java или любом другом языке высокого уровня .dump не работает. Нам нужно закодировать преобразование в CSV вручную. Я привожу пример с Python. Другие, примеры будут оценены:

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

Если у вас есть данные панели, другими словами, многие отдельные записи с идентификаторами добавляют это к внешнему виду, а также выводит сводную статистику:

        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 
Автор: Davoud Taghawi-Nejad Размещён: 14.05.2014 03:14

0 плюса

1945 Репутация автора

Ответ от retracile должен быть самым близким, но он не работает для моего случая. Один запрос вставки просто сломался в середине, и экспорт просто остановился. Не уверен, в чем причина. Тем не менее, он прекрасно работает во время .dump.

Наконец, я написал инструмент для разделения SQL, сгенерированного из .dump:

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

Автор: Walty Yeung Размещён: 17.02.2015 03:42

2 плюса

15247 Репутация автора

Обзор других возможных решений

Включить только ВСТАВКИ

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

Легко реализовать, но не получится, если в любой из ваших столбцов появятся новые строки

Режим вставки SQLite

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

Это хорошее и настраиваемое решение, но оно не работает, если у ваших столбцов есть объекты BLOB-объектов типа Geometry в пространственном объекте.

Diff the dump со схемой

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

Не уверен почему, но у меня не работает

Другое (новое) возможное решение

Возможно, нет лучшего ответа на этот вопрос, но тот, который мне подходит, это grep вставки, принимая во внимание, что это новые строки в значениях столбцов с таким выражением

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

Для выбора таблиц .dumpдопускается использование параметра LIKE для соответствия именам таблиц, но если этого недостаточно, возможно, лучше использовать простой скрипт

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

или что-то более продуманное, чтобы уважать внешние ключи и инкапсулировать весь дамп только в одной транзакции

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

Учтите, что выражение grep не будет выполнено, если );в каком-либо из столбцов присутствует строка

Восстановить его (в базе данных с уже созданными таблицами)

sqlite3 -bail database.db3 < /tmp/backup.sql
Автор: Francisco Puga Размещён: 18.05.2016 10:23

4 плюса

581 Репутация автора

В соответствии с документацией SQLite для командной строки. Для SQLite вы можете экспортировать таблицу SQLite (или часть таблицы) как CSV, просто установив для «mode» значение «csv», а затем выполните запрос для извлечения нужных строк стол:

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

Затем с помощью команды «.import» импортируйте данные CSV (значения, разделенные запятыми) в таблицу SQLite:

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

Пожалуйста, прочтите дополнительную документацию о двух рассматриваемых случаях: (1) таблица «tab1» ранее не существует и (2) таблица «tab1» уже существует.

Автор: PeterCo Размещён: 19.01.2017 09:38
Вопросы из категории :
32x32