Save query result from QGIS to csv

Question

I have a layer saved to spatialite which I want to query and save part of to csv.

Even the query result takes a very long time to load to the browser (2.5M rows) even if I load it without geometry.

Is there a way to use the SQL engine DB Manager has to save the query result to file?

I already tried the sqlite way with the example I found here.
and the COPY TO way PostgreSQL uses specified here.

Is there another way I am missing? I thought QGIS DB Manager uses an SQLite engine.

Using QGIS 3.2.1 on Windows.

Answer 1

I claim the key problem is to build the table view resulting from your query, not the query itself. 'Extract by expression' algorithm in QGIS 3 is written as a native C++ algorithm and as such VERY FAST. In contrary to 'Select by expression' you don't generate a selection in QGIS 3 and then 'Save layer...' with 'Save only selected objects' enabled. 'Extract by expression' will save the query result directly to the format you desire which may take some time too as well, but you avoid creating a temporary table view in the attribute table or some query tool.

Answer 2

While @Jochen Schwarze's answer is good, it did not answer the question of how to perform an SQL query on the data.
since i don't have SQLite installed i used QGIS' python interpreter to read the data from the sqlite(spatialite, since geometry existed) file and write it to csv.

sqlite_file = 'path/to/test.sqlite'
csvfile = 'path/to/test.csv'

import sqlite3
import csv

conn = sqlite3.connect(sqlite_file)
conn.enable_load_extansion(True)
conn.execute('SELECT load_extension("mod_spatialite")')
conn.execute('SELECT InitSpatialMetaData(1);')
c = conn.cursor()

data = c.cursor('select column1, column2, aswkt(GEOMETRY) geom from test;')

with open(csvfile, 'w', newline='', encoding='utf-8') as f:
    writer = csv.writer(f)
    writer.writerow(['column1','column2', 'geom'])
    writer.writerows(data)

conn.close()

this allowed me to read and convert the large file without having to add it to the workspace in QGIS.