I want to parse the JSON column in Athena but I have a problem in one column. The column includes an escape character. I don’t know if this problem. I want to reach to Message. You can see below sample data and Message under Return. Thanks This is the JSON. Answer If you take only the object which conta…
Tag: python
Select some datetime from base
I have a 2 table in my base. The first table is reservation table. The start_ts and end_ts are time when start and end the reservation of desk (desk_id): [Reservation_table] The second is motion, which come from sensors motion. [Motion_table] This 2 tables are connecting that the sensors are start save to the…
how to iterate through geojson elements
i want to execute the code in this question https://gis.stackexchange.com/questions/142391/storing-geojson-featurecollection-to-postgresql-with-postgis/142479#142479 but when i run the app i receive the following error: please let me know how to fix it. code: attempts: Answer From the database perspective the…
How to execute custom logic at pyspark window partition
I have a dataframe in the format shown below, where we will have multiple entries of DEPNAME as shown below, my requirement is to set the result = Y at the DEPNAME level if either flag_1 or flag_2= Y, if both the flag i.e. flag_1 and flag_2 = N the result will be set as N as shown for DEPNAME=personnel
Python code to send data from I2C sensor to a local SQL database
I am working on a system of I2C sensors connected together and communicating to a raspberry pi4B. With the code below I am able to save the measurements in a excel file. I would like to store them in a table inside a sql database that I have created locally on my laptop. What should I change in this code?
Cannot INSERT strings with Set Clause with mariadb connector (python)
I want to insert a new row in my table by using the python-mariadb connector. For that I prefer to use the SET clause. For some reason it does work if I only want to save ints (i.e y=2), but when I use a string, the following error occurs Unknown column ‘myString’ in ‘field list’ It se…
Compare date of input type datetime with date in SQL using flask
I use this .html page to get input type datetime: And my .py code: my DB: DB Example I want to show only all data of ‘sessioncreate’ when only date of ‘sdate’ equal of today date How can i achieve that? Answer Use the DATE() function. This way you can convert the DATETIME in your datab…
First row of all groups in Flask SQLAlchemy
I want to group the database by gauge_id and then get the row having maximum time_col. This is my current code: But I am only able to get both gauge_id and time_col. When I add another column (reading), like this: it gives this error. How do I return the top row of each groups? Thanks in advance. Answer One w…
How to create a complex aggregate function in sqlite
Suppose I have the following table where there are two sets of observation dates (2015-01-01, 2016-01-01) in the first column. For each observation date, there are associated item_date and item_value. observation_date item_date item_value 2015-01-01 2012-12-31 0 2015-01-01 2013-03-31 1 2015-01-01 2013-06-30 2…
None-unique column index error using cur.fetch_pandas_all() to extract data from Snowflake
I’m pulling results from Snowflake using the code below. The SQL statement returns fine in Snowflake Web UI but when pulled in Python I get a ValueError: Found non-unique column index If this happened in my Pandas workflow, I would reset_index() but, as its happening at the point of the dataframe being …