2

I am using DataGrip or SQLiteStudio (database managers) to run a series of queries in a database which guide me to find the information that I require. The queries works well and the results are shown in the console of the Dabase Manager. However, I need to export the results that appears in the database manager console into a CVS file.

I have seen everybody works directly in the shell, but I need (I have to) to use a DB manager to run the queries (so far the queries that I need to run in one step are about 600 lines).

In the sqlite3 shell I am able to run (and works) (.headers on) (.mode csv) (.output C:/filename.csv) (select * from "6000_1000_Results";) (.output stdout)

However, running this code in the sql editor of the DB manager, doesn´t work at all.

--(.....)
--(around 600 lines before)
--(.....)
    "Material ID",
    "Material Name",
    SUM("Quantity of Material")  Quantity
FROM
     "6000_1000_Results_Temp"
GROUP BY
  "DataCenterID", "Material ID";

------------------------------------------------------------
--(HERE IS WHERE I NEED TO EXPORT THE RESULTS IN A CVS FILE)
------------------------------------------------------------
.headers on
.mode csv
.output C:/NextCloudLuis/TemproDB.git/csvtest.csv
select * from "6000_1000_Results";
.output stdout
.show

DROP TABLE IF EXISTS "6000_1000_Results_Temp";
DROP TABLE IF EXISTS "6000_1000_Results";

Datagrip do not show any error, it runs the queries in a few seconds, but there´s no file anywhere, SQLiteStudio gives a syntax error.

belgariontheking
  • 1,351
  • 1
  • 10
  • 15
Lucho
  • 221
  • 1
  • 6
  • I wouldn't expect the sqlite shell dot commands to work in anything but the sqlite shell. Have you read through the documentation of the alternative programs you're using to see if they support export to csv? – Shawn Aug 07 '19 at 10:58
  • Yes, The DB manager can export the result into a cvs file, with some clics, but at the end I need to do a script with python, and the final user (my boss) only need to double clic on the script and get some calculations and the data (which is my problem) in a cvs file – Lucho Aug 07 '19 at 11:37
  • Do you need to export several queries to CSV files AT ONCE? – moscas Aug 08 '19 at 08:27
  • Yes, I have like 30 different queries (that manage, organize and get the info that I am looking for). In DataGrip, I execute in one step all those queries. At the end, I just extract information of one table which is the information I need. I need to export that final info in a CVS file using a final query. – Lucho Aug 08 '19 at 10:31
  • Well, so far the solution I have done, is using Python. In python I execute the 30 queries, and then, I save the final information in a data frame. Finally, I export the data frame into a CVS or Excel file. Do you think guys I should leave this topic open? Maybe someone knows how to do it directly in Datagrip or any data manager through a query. – Lucho Aug 08 '19 at 10:35

1 Answers1

1

Finally I solve this issue in the next steps: I run over python all the queries using the lybrary sqlite3. Then the result of all the queries are saved in a pandas dataframe. Then the pandas dataframe is exported to a cvs and xlxs file.

Here is the python code:

import sqlite3
import queries

conn = sqlite3.connect("tempro.db")  #make the database connection to python
level4000 = queries.level4000to1000(conn) #I call the function in queries.py
level4000.to_csv('Level4000to1000.csv') #export result ro cvs
level4000.to_excel('Level4000to1000.xlsx') #export result ro xlsx
conn.close()

and here is the python file where I save all the queries (queries.py)

import sqlite3
from sqlite3 import Error
import pandas as pd


def level4000to1000(conn):

    cur = conn.cursor()
    cur.executescript(
        """
            /* Here I put all the 600 lines of queries */
            DROP TABLE IF EXISTS "4000_1000_Results_Temp";
            DROP TABLE IF EXISTS "4000_1000_Results";

            /* Here more and more lines */
            --To keep the results from all queries
            CREATE TABLE "4000_1000_Results_Temp" (
                "DeviceID"      INTEGER,
                "Device Name"   TEXT,
                             SUM("Quantity of Material")  Quantity
            FROM
                 "4000_1000_Results_Temp"
            GROUP BY
              "DeviceID", "Material ID";
            """)

    df = pd.read_sql_query('''SELECT  * FROM "4000_1000_Results";''', conn)
    cur.executescript("""DROP TABLE IF EXISTS "4000_1000_Results_Temp";
                      DROP TABLE IF EXISTS "4000_1000_Results";""")
    return df #returns a dataframe with the info results from the queries

In the end, it seems that there is no way to export results to file formats as cvs using SQL coding.

Lucho
  • 221
  • 1
  • 6