Page 1 of 1

Queries and scraping to tables

Posted: Wed Oct 16, 2019 4:58 am
by durlecs
I have a couple of questions about queries.

1. I want to enter data into a text field on a website. The data is stored in the database as three separate columns - Column1, Column2, and Column3. I need to add them all together into a string so that it reads (Column1 + " " + Column2 + " " Column3) and then enter that into a search field on a website. There will be multiple rows in the table. Once the scraper has completed the scraping for each row, I want to set a flag that denotes it has been scraped. I will be adding to this table manually on a regular basis. I want to scrape for each row that does not have this flag set. When I scrape, I want to add data to this table or have it related to the table. How do I do this?

2. How do I find the syntax for queries? For example, I am trying to do this and I keep getting an error:

SELECT * FROM tablename ORDER BY column DESC LIMIT 1

Is there somewhere I can see the exact syntax for different types of queries?

Re: Queries and scraping to tables

Posted: Wed Oct 16, 2019 1:39 pm
by webmaster
1. First, make sure to use the Query prefix instead of the Data one when querying your database:

Code: Select all

as (column1 column2 column3)
If you're using the Fill Form wizard, Helium will do this for you. Otherwise, if you already have the tables, just right click the table set and select Create Query, and then you'll be able to access this data using the Query prefix. Then you'll be able to simply do this to concatenate:

Code: Select all

   ·  column1
   ·  column2
   ·  column3
You can't use the wizard to generate that code since there's no concatenate option, but you can use a single column with the wizard and then replace it with the code above. Or it may even be easier to create a query that already includes the concatenated piece of data:

SELECT `column1` || `column2` || `column3` FROM `Main`

In order to set a flag, I guess you could just add a column to your extract action with some literal value such as this:

Code: Select all

Then the flag will be "my_flag" when extracted and blank when you add a row manually.

2. Helium uses SQLite syntax so your query should work. Try wrapping your table and column names with the ` character. Also, you may need to include the full table name:

SELECT * FROM `Main.children` ORDER BY `column` DESC LIMIT 1

Re: Queries and scraping to tables

Posted: Wed Oct 16, 2019 4:53 pm
by durlecs
Here's how I got it to work for anyone interested:

SELECT * FROM 'tablename' ORDER BY 'tablename'.'column' DESC LIMIT 1