DBMS – Page 309

Export an Entire SQLite Database to an SQL File

May 28, 2020April 23, 2020 by Ian

The SQLite command line provides several methods for exporting or backing up an entire database. One of those involves the .dump dot command.

The .dump command enables you to convert a whole database to a single ASCII text file. In other words, it renders the whole database as SQL. The text file contains all the SQL statements required to create the tables, insert data, etc.

You can also use .dump to create a compressed file. I outline both of these methods below.

Export SQLite Query Results to a CSV File

May 28, 2020April 22, 2020 by Ian

When using the SQLite command line shell, you can export your query results to a CSV file by using the .mode dot command in conjunction with the .output or .once commands.

You can also use the .system command to open that file.

Automatically Open SQLite Query Results in a Text Editor

May 28, 2020April 21, 2020 by Ian

You can configure the SQLite command line interface to automatically open query results in a text editor.

When you do this, you have two options:

Use a temporary file
Use a permanent file (i.e. save it for later)

This article provides examples of both methods.

Automatically Open SQLite Query Results in Excel

May 28, 2020April 20, 2020 by Ian

The SQLite command line interface has a handy little feature where you can open your SQL query results in an Excel file.

When you run a query, instead of the query being output to your console as it normally would, it instead opens as an Excel spreadsheet (or LibreOffice, or whatever program your system uses to open CSV files).

You have two options when doing this:

Use a temporary file
Use a permanent file (i.e. save it for later)

This article covers both options.

Save SQLite Query Results to a Text File

May 28, 2020April 19, 2020 by Ian

You can use the .output or .once dot commands to save your query results to a text file when using the SQLite CLI.

How to Format SQLite Query Results in TCL Mode

May 28, 2020April 18, 2020 by Ian

The SQLite command line interface allows you to format your query results in TCL mode. Doing this encloses all output in double quotes, including the column headers if you’ve specified those. Any internal double quotes are escaped with a backslash.

Output SQLite Query Results as a Tab-Separated List

May 28, 2020April 17, 2020 by Ian

The SQLite command line interface allows you to use various modes for formatting the result set of SQL queries. For example, you can format the results in columns, as a comma separated list, using vertical output, and more.

This article explains how to use the .mode dot command to format your results as a tab-separated list.

Display SQLite Query Results using Vertical Output

May 28, 2020April 16, 2020 by Ian

One of the values you can provide to the .mode dot command when using the SQLite command line shell is line.

Using .mode line outputs the query results using vertical output. What I mean is that, each column is displayed on a new line. The column name is displayed, along with an equals sign and then the column’s value.

Output SQLite Query Results as an INSERT Statement

May 28, 2020April 15, 2020 by Ian

The SQLite command line interface has a handy dot command called .mode, which allows you to change the way query results are formatted.

One of the values you can use with this command is insert. Using this value results in all subsequent query results being formatted as an SQL INSERT statement.

Format SQLite Query Results as an HTML Table

May 28, 2020April 14, 2020 by Ian

The SQLite command line shell has a .mode dot command, which enables you to change the way SQLite formats its query results.

The default output for queries is as a pipe-separated list, however, you can use the .mode dot command to change this to another format, such as an HTML table.