howto insert data into a database?

[Phil Pan]

Hi all, I can't seem to figure this out and I would appreciate any help.

I have a ruby script that I want to cron on a server. This script will
scrape data from a website and then insert the data into a MySQL
database.

I have successfully scrapped the data however I cannot figure out how to
insert the data into the MySQL database

From command prompt I figured out that that this is the syntax to insert
a file into a database:

mysql -u root -pqwerasdf ridespot --execute="LOAD DATA LOCAL INFILE
"c:/new.txt\" INTO TABLE foo LINES TERMINATED BY '\r\m'"

How do I run this command in ruby?

I have already tried:

system "command"
and
'command'

But it doesn't work.

Thanks for your response!

-Phil

 

[Matt Todd]

Hi all, I can't seem to figure this out and I would appreciate any help.

I have a ruby script that I want to cron on a server. This script will
scrape data from a website and then insert the data into a MySQL
database.

I have successfully scrapped the data however I cannot figure out how to
insert the data into the MySQL database

From command prompt I figured out that that this is the syntax to insert
a file into a database:

mysql -u root -pqwerasdf ridespot --execute="LOAD DATA LOCAL INFILE
"c:/new.txt\" INTO TABLE foo LINES TERMINATED BY '\r\m'"

How do I run this command in ruby?

I have already tried:

system "command"
and
'command'

But it doesn't work.

If you'd like to not just execute a command in the Ruby script and
have it just be actual Ruby, check out the Sequel library (
http://sequel.rubyforge.org/ should be one of the URLs though it is a
Google Code project).

If you want to execute a command in the Ruby script on the
commandline, enclose the command in backticks (shares keys with the
Tilde on my keyboard):

`mysql -u root -p123456 -D databasename --execute='...'`

There may be errors with the actual SQL though which may be causing
problems that aren't being shown to you. Try putting "puts" in front
of the command you call so the returned results are shown to you. You
may have to redirect 2>&1.

Cheers,
Matt Todd

 

[Phil Pan]

Thanks for the tip, I'll look it.
I also found the problem.

Instead of:

system("mysql -u root -pqwerasdf ridespot --execute=\"LOAD DATA LOCAL
INFILE \"c:/new.txt\" INTO TABLE foo LINES TERMINATED BY '\r\m'\"")

I needed to write:

system("mysql -u root -pqwerasdf ridespot --execute=\"LOAD DATA LOCAL
INFILE \\\"c:/new.txt\\\" INTO TABLE foo LINES TERMINATED BY '\r\m'\"")

Notice how there are 2 extra back slashes...
Pretty silly.

 

[Thufir]

Oooh, I really like your idea. Is this open source?


-Thufir

 

[Thufir]

I needed to write:

system("mysql -u root -pqwerasdf ridespot --execute=\"LOAD DATA LOCAL
INFILE \\\"c:/new.txt\\\" INTO TABLE foo LINES TERMINATED BY '\r\m'\"")

Thank you, I'm sure that this will come in handy to insert data into
mysql with ruby


-Thufir