I have a sheet with rows (id and year). See below: id year 101 2002 101 2006 101 2010 101 2014 101 2018 102 2002 102 2006 102 2010 102 2014 103 2010 I simply want to regroup and reformat my table to look like this: id 2002 2006 2010 2014 2018 101 1 1 1 1 1 102 1
Tag: sqlite
Select if date is older than 30 days. What to do if format is wrong?
I want to select all entries where the column “date” is older than 30 days. The problem is, that I dont have the date saved in a supported format. My date is saved as ‘DD.MM.YYYY hh:mm:ss’. Is there a way to get such a selection within sqlite without changing the give database? Or is m…
SQL MAX(col1, col2) with priorities
I need to select row with MAX(val1) for each group. If there is more than one row with equal val1 = MAX(val1) I need to select from them row with MAX(val2), etc. In pseudo code it should be like this: For example: I have table nums with one TEXT column name and three INTEGER columns — id, num1 and num2
Python SQL: storing objects or iterables
Assume I want to store via sqlite3 a simple list of strings: or a python object with properties and methods. What I tried so far is to serialize (pickle) my_list, and the returned byte representation is b’x80x03]qx00(Xx01x00x00x00aqx01Xx01x00x00x00bqx02Xx01x00x00x00cqx03e.’. However, cannot be st…
Group items by 2 columns or similarity with another column
Let’s say we have a Sqlite table containing: How to group by h=CONCAT(city,age) but also add in the group the users that have a column foo identical to someone in the group? As seen in Group items by 2 columns, here is how to group by h: How to add this second condition on foo? Example: here Alice and B…
SQL query for entries of last N jobs
I have the following table: etc, so basically jobs keep adding entries concurrently I am looking for an efficient query to return all entries of the latest (by time) N jobs So find the first (by time) entry for each job, then using that get the latest N jobs with all their entries So for the above table and N…
How to add data to a field based on other fields in a SQL database
I have an SQLite table called wcvp constructed from a csv file downloaded from the World Check List of Vascular Plants (see https://wcvp.science.kew.org/ and http://sftp.kew.org/pub/data-repositories/WCVP/). When I run this query: I get this result: kew_id genus species infraspecies 304293-2 Quercus robur 771…
Unable to use Cursor in separate function
I’m currently tryign to learn how to use SQLite3 and am trying to seperate setting up the DB through functions. My second function is where I’m having the error : AttributeError: ‘NoneType’ object has no attribute ‘cursor’. After looking at the SQLite3 doucmentation, I got …
INSERT + SELECT data type mismatch on similar fields
I’m running the following SQLite workaround to add a primary key to a table that did not have one. I am getting a datatype mismatch on However, the fields have exactly the same type. Is it possible that his happens due to running the queries from DbBrowser for SQLite? Answer You have defined the column …
sqlite3 INSERT INTO UNION SELECT
Table columns: pk is the auto-incrementing primary key An error occurs when I execute this SQL DB Error: 1 “table table1 has 4 columns but 3 values were supplied” Answer You should explicitly specify which columns in table1 your insert is targeting: When you omit the select list entirely, SQLite w…