Trouble exporting fsql(query).run() query

[ahershy]

I can see the first few rows printed in my IDE when using fsql(query).run(). However, I would like to export the whole query. How would I go about exporting to excel or text? It's a lot trickier than I thought. Below is the code and query I'm using. Everything works perfectly as planned, except I cannot export.

from fugue_sql import fsql
query = 'SELECT point, distancefrom, isitland FROM df WHERE isitland = "True" ORDER BY distancefrom LIMIT 50 PRINT'
fsql(query).run()

PandasDataFrame
point:str |distancefrom:double|isitland:str
-------------------------------------------------------------------+-------------------+------------
(53.6117, -166.59818) |1104.5381558307515 |True
(53.57895, -166.82095) |1106.2604760111058 |True
(53.6373, -166.76843) |1109.0255797929005 |True
(53.36486, -168.17752) |1118.1591199262448 |True
(53.17287, -168.7642) |1118.6132248530207 |True
(53.22795, -168.61332) |1118.7472352194661 |True
(53.90554, -166.36213) |1119.2786304419692 |True
(54.15937, -165.50491) |1122.6569154945525 |True
(53.97564, -166.36982) |1123.8576728572605 |True
(54.20736, -165.39408) |1124.2129127564162 |True
DataFrames()

[kvnkho]

Hi @ahershy

You can use the SAVE keyword as shown here. If you need more help with this, just let me know.

If you need to pull out the DataFrame:

from fugue_sql import fsql
import pandas as pd

df = pd.DataFrame({"a": [1,2,3], "b": [1,2,3]})

res = fsql("""
           SELECT *
             FROM df 
            WHERE a > 2
            YIELD DATAFRAME AS test
""").run()

print(res["test"].as_pandas().head())

[ahershy]

Apologies, I still cannot save. I'm relatively new to python so thanks for bearing with me... Would you mind comparing the 2 statements below and letting me know why the 'run' works but the 'save does not?

#run (works)
query = 'SELECT point, distancefrom, isitland FROM df WHERE isitland = "True" ORDER BY distancefrom LIMIT 50 PRINT'
fsql(query).run()

#save (doesn't work)
query = 'SELECT point, distancefrom, isitland FROM df WHERE isitland = "True" ORDER BY distancefrom LIMIT 50 SAVE dff OVERWRITE TO SINGLE CSV "C:\Users\Andrew\Desktop\blank.csv"'
fsql(query).run()

Thanks

[kvnkho]

For Windows, you may need double backslash for your path.

C:\\Users\\Andrew\\Desktop\\blank.csv"

Also, I think it's OVERWRITE SINGLE CSV without TO. Also, the Fugue Slack may be a better way to post if you want a faster response: slack.fugue.ai .

Do you get an error?

[ahershy]

Still getting an error, please see image below.

This is what I tried:
query = 'SELECT point, distancefrom, isitland FROM df WHERE isitland = "True" ORDER BY distancefrom LIMIT 50 SAVE df OVERWRITE SINGLE CSV "C:\\Users\\Andrew\\Desktop\\blank.csv"'
fsql(query).run()

[goodwanghan]

Yes, this is expected. This is due to the trickiness of \ in windows and in programming languages.

I have explained it here #331 (comment)

You are in two level escape, you need either r"" or \\\\

[ahershy]

I tried the same thing with 4 slashes instead of 2 and it worked!! Thank you