Queries and scraping to tables

Questions & Answers about Helium Scraper 3
Post Reply
durlecs
Posts: 11
Joined: Sat Sep 28, 2019 2:11 am

Queries and scraping to tables

Post by durlecs » Wed Oct 16, 2019 4:58 am

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?

webmaster
Site Admin
Posts: 491
Joined: Mon Dec 06, 2010 8:39 am
Contact:

Re: Queries and scraping to tables

Post by webmaster » Wed Oct 16, 2019 1:39 pm

1. First, make sure to use the Query prefix instead of the Data one when querying your database:

Code: Select all

Query.Main2
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

extract
   flag
      "my_flag"
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
Juan Soldi
The Helium Scraper Team

durlecs
Posts: 11
Joined: Sat Sep 28, 2019 2:11 am

Re: Queries and scraping to tables

Post by durlecs » Wed Oct 16, 2019 4:53 pm

Here's how I got it to work for anyone interested:

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

Post Reply