Jump to content



Photo

Explode Table Rows For Sql Insert

table sql loop

  • Please log in to reply
4 replies to this topic

#1 juno

juno

    Newbie

  • Members
  • Pip
  • 9 posts
  • OS:Windows 10
  • Total Memory:More Than 9Gb
  • Framework:v4.5+, unsure
  • License:Developer Edition

Posted 27 January 2018 - 09:08 PM

I have scraped an html table with as many as 100 rows with 5 columns. Practically everything I have been trying treats the entire table as one variable. For instance, I'm able to write the table to a file.

 

What I want to be able to do is loop through each row, one by one and explode out the row to 5 variables: cell1, cell2, cell3, cell4, cell5.

 

I then want to use these (while still remaining in the loop for this row) in a SQL query:

INSERT INTO db_table (col1, col2, col3, col4, col5) VALUES ('cell1', 'cell2', 'cell3', 'cell4', 'cell5')

 

Seems very simple but I can't quite make this happen in Ubot. How do you break up (or explode) table rows out into variables like that?

 

Thanks.



#2 agustinusdwisusanto

agustinusdwisusanto

    Newbie

  • Members
  • Pip
  • 2 posts
  • OS:Windows 7
  • Total Memory:1Gb
  • Framework:v4.5+, unsure
  • License:Developer Edition

Posted 28 January 2018 - 12:08 AM

hai juno

​i have same problem

​i hope get solve

​thank you

​agustinus



#3 Code Docta (Nick C.)

Code Docta (Nick C.)

    Member for Hire

  • Fellow UBotter
  • PipPipPip
  • 1460 posts
  • LocationIn a Python or UBot IDE or ubotdocta.com
  • OS:Windows 10
  • Total Memory:More Than 9Gb
  • Framework:v4.5+, unsure
  • License:Developer Edition

Posted 28 January 2018 - 03:02 AM

Here you go,

$table cell is the function you are looking for.

save to file("{$special folder("Desktop")}\\test-data.csv","r1,cell 2,cell 3,cell 4,cell 5
r2,cell 2,cell 3,cell 4,cell 5")
comment("INSERT INTO db_table (col1, col2, col3, col4, col5) VALUES (\'cell1\', \'cell2\', \'cell3\', \'cell4\', \'cell5\')")
clear table(&goes into DB)
create table from file("{$special folder("Desktop")}\\test-data.csv",&goes into DB)
set(#table row index,0,"Global")
loop($table total rows(&goes into DB)) {
    set(#sql command,"INSERT INTO db_table (col1, col2, col3, col4, col5) VALUES (\'{$table cell(&goes into DB,#table row index,0)}\', \'{$table cell(&goes into DB,#table row index,1)}\', \'{$table cell(&goes into DB,#table row index,2)}\', \'{$table cell(&goes into DB,#table row index,3)}\', \'{$table cell(&goes into DB,#table row index,4)}\')","Global")
    alert(#sql command)
    increment(#table row index)
}


Regards,
CD

Attached Files


UBot Expert Tutorials Store

 

ubot-docta-header-ubot-expert-store.png

 

Python and UBot expert tutorials and documentation


#4 juno

juno

    Newbie

  • Members
  • Pip
  • 9 posts
  • OS:Windows 10
  • Total Memory:More Than 9Gb
  • Framework:v4.5+, unsure
  • License:Developer Edition

Posted 29 January 2018 - 02:32 PM

Thanks so much Code Docta! One question: If I already have the data in a table form from scraping and html table, why would I need to do these steps where I write it to a file just to read it back into a table again?

 

save to file("{$special folder("Desktop")}\\test-data.csv","r1,cell 2,cell 3,cell 4,cell 5
r2,cell 2,cell 3,cell 4,cell 5")
comment("INSERT INTO db_table (col1, col2, col3, col4, col5) VALUES (\'cell1\', \'cell2\', \'cell3\', \'cell4\', \'cell5\')")
clear table(&goes into DB)
create table from file("{$special folder("Desktop")}\\test-data.csv",&goes into DB)





#5 Code Docta (Nick C.)

Code Docta (Nick C.)

    Member for Hire

  • Fellow UBotter
  • PipPipPip
  • 1460 posts
  • LocationIn a Python or UBot IDE or ubotdocta.com
  • OS:Windows 10
  • Total Memory:More Than 9Gb
  • Framework:v4.5+, unsure
  • License:Developer Edition

Posted 29 January 2018 - 07:06 PM

You are welcome :)


you don't :) "$table cell is the function you are looking for."

Just easier to give entire example, just use what you need*($table cell). The rest is for the sake of the example.


UBot Expert Tutorials Store

 

ubot-docta-header-ubot-expert-store.png

 

Python and UBot expert tutorials and documentation






Also tagged with one or more of these keywords: table, sql, loop

0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users