# Howto insert data into a database? **URL:** \ **Category:** ruby-talk\ **Created:** [17 January 2008 17:13 UTC](https://rubytalk.org/t/howto-insert-data-into-a-database/43849 "2008-01-17T17:13:35Z")\ **Posts on this page:** 5\ **Page:** 1
**Author:** ![Phil\_Pan](https://avatars.discourse-cdn.com/v4/letter/p/73ab20/32.png) [@Phil\_Pan](https://rubytalk.org/u/Phil_Pan)\ **Post date:** [17 January 2008 17:13 UTC](https://rubytalk.org/t/howto-insert-data-into-a-database/43849/1 "2008-01-17T17:13:35Z")
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 > **···** > > -- > Posted via [http://www.ruby-forum.com/](http://www.ruby-forum.com/). ---
**Author:** ![Matt\_Todd](https://avatars.discourse-cdn.com/v4/letter/m/ed8c4c/32.png) [@Matt\_Todd](https://rubytalk.org/u/Matt_Todd)\ **Post date:** [17 January 2008 18:09 UTC](https://rubytalk.org/t/howto-insert-data-into-a-database/43849/2 "2008-01-17T18:09:13Z")
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/](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 > **···** > > On Jan 17, 2008 12:13 PM, Phil Pan \ wrote: > > > 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. ---
**Author:** ![Thufir](https://avatars.discourse-cdn.com/v4/letter/t/ee7513/32.png) [@Thufir](https://rubytalk.org/u/Thufir)\ **Post date:** [17 January 2008 22:19 UTC](https://rubytalk.org/t/howto-insert-data-into-a-database/43849/3 "2008-01-17T22:19:56Z")
Oooh, I really like your idea. Is this open source? -Thufir ---
**Author:** ![Phil\_Pan](https://avatars.discourse-cdn.com/v4/letter/p/73ab20/32.png) [@Phil\_Pan](https://rubytalk.org/u/Phil_Pan)\ **Post date:** [17 January 2008 19:12 UTC](https://rubytalk.org/t/howto-insert-data-into-a-database/43849/4 "2008-01-17T19:12:59Z")
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. Matt Todd wrote: > **···** > > > On Jan 17, 2008 12:13 PM, Phil Pan \ wrote: > > > > > a file into a database: > > > '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/](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 > > -- > Posted via [http://www.ruby-forum.com/\](http://www.ruby-forum.com/%5C). ---
**Author:** ![Thufir](https://avatars.discourse-cdn.com/v4/letter/t/ee7513/32.png) [@Thufir](https://rubytalk.org/u/Thufir)\ **Post date:** [22 February 2008 09:09 UTC](https://rubytalk.org/t/howto-insert-data-into-a-database/43849/5 "2008-02-22T09:09:58Z")
Thank you, I'm sure that this will come in handy to insert data into mysql with ruby 🙂 -Thufir > **···** > > On Fri, 18 Jan 2008 04:12:59 +0900, Phil Pan wrote: > > > 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'\"")