How do I insert an array into database table?

Question

I have the following Ruby code:

 94   def open_connection(info)
 95     self.log "opening #{info.inspect}"
 96     db = Mysql.init
 97     db.options(Mysql::SET_CHARSET_NAME, 'utf8')
 98     db.real_connect(info.host, info.user, info.password, info.dbname, info.port)
 99     db.query("SET NAMES utf8")
100 »···res = db.query("SELECT realname FROM profiles")
101 
102 »···conn = PGconn.connect("localhost", 5432, '', '', "dbname", "user", "pwd")

How do I take the res variable and loop through it to insert it into my Postgres database?

I'd highly recommend using an ORM, except for the most trivial of DB tasks. I like Sequel, but Datamapper and ActiveRecord are good also. The big win with a good ORM, is your code doesn't change when you need to switch to a different DBM, you just point it and it goes. — the Tin Man
– the Tin Man, Commented Apr 27, 2012 at 18:34
@theTinMan: The downside of an ORM is that it is a lot of wasted effort for a one-off and it can be difficult to get them to talk to two different databases at the same time. ORMs also tend to have problems with database-specific data types (such as PostgreSQL's rich but non-standard types) and advanced SQL features (such as triggers and window functions). I'll qualify this by noting that most of my Ruby ORM experience is (unfortunately) with ActiveRecord, the others might have better attitudes and be less limited. — mu is too short
– mu is too short, Commented Apr 27, 2012 at 18:45
FWIW, Sequel is not hard to get to talk to multiple databases, and I feel that there is generally less typing for the same work as raw SQL statements. It's win-win (and no, using just it's datasets it's not really a full ORM, which is why it can be so efficient and easy to use). — Phrogz
– Phrogz, Commented Apr 27, 2012 at 18:47
@theTinMan: "your code doesn't change when you need to switch to a different DBM", you really should know better than that, a quick "ruby-on-rails postgresql heroku" search is all you need to see how portable ORMs are between databases. — mu is too short
– mu is too short, Commented Apr 27, 2012 at 18:47
"The downside of an ORM is that it is a lot of wasted effort for a one-off", that's why I said "except for the most trivial of DB tasks". I've had no problem getting Sequel to talk to multiple DBs and/or servers. ActiveRecord does have its opinions, which is why I use Sequel, because it doesn't get in my way. — the Tin Man
– the Tin Man, Commented Apr 27, 2012 at 18:48

mu is too short · Accepted Answer · 2012-04-27 20:49:40Z

3

The MySQL query method gives you a Mysql::Result instance so you can use each to get each row as an Array:

res = db.query('select realname from profiles')
res.each do |a|
    # a is an array of strings
    realname = a.first
    # ...
end

If you had more columns in the result set then each_hash might be easier to work with, that calls the block with a Hash which maps column names to their values.

To insert data into PostgreSQL, use exec:

pg_conn.exec('insert into some_table (some_column) values ($1)', [ realname ])

Please use placeholders rather than interpolation, there's no need to party like PHP programmers in 1999.

You could also use prepare and exec_prepared:

pg_conn.prepare('ins', 'insert into some_table (some_column) values ($1)')
pg_conn.exec_prepared('ins', [ realname1 ])
pg_conn.exec_prepared('ins', [ realname2 ])
# ...

The prepare approach is better if you're going to be executing the same SQL over and over again.

Combining them gets the job done:

res = db.query('select realname from profiles')
pg_conn.prepare('ins', 'insert into some_table (some_column) values ($1)')
res.each { |a| pg_conn.exec_prepared('ins', a) }

edited Apr 27, 2012 at 20:49

answered Apr 27, 2012 at 18:40

mu is too short

436k71 gold badges863 silver badges822 bronze badges

Sign up to request clarification or add additional context in comments.

9 Comments

wonbyte Over a year ago

so do: res = db.query('select realname from profiles') res.each do |a| realname = a.first end pg_conn.prepare('ins', 'insert into some_table (some_column) values ($1)') res.each { |a| pg_conn.exec_prepared('ins', a) } Is this correct?

mu is too short Over a year ago

@wonbyte: You don't need the res.each do |a| # a is an..., just res = db.query(...); pg_conn.prepare('ins', ...); res.each { |a| ... }.

wonbyte Over a year ago

So the first line of your code at the top and then the last two lines at the bottom? Sorry, Brand new to Ruby

mu is too short Over a year ago

@wonbyte: Yeah, the res.each in the first chunk of code is for illustrative purposes.

wonbyte Over a year ago

I have pg_conn.prepare('users', 'INSERT INTO users (mail) VALUES ($1)') 108 »···res.each { |a| pg_conn.exec_prepared('users', a) } but It's not putting the data in. Any suggestions?

|

Phrogz · Accepted Answer · 2012-04-27 18:50:50Z

2

Using the Sequel DB library you can either use import:

# When `res` is an array of just values
res = MYSQLDB[:profiles].select_map(:realname)
PGDB[:profiles].import([:realname],res)

…or you can use multi_insert:

# When `res` is an array of hashes mapping column name to value
res = MYSQLDB[:profiles].select(:realname).all
PGDB[:profiles].multi_insert(res)

With either of the above you can optionally pass a last parameter with the option to insert in batches (which may use less memory and be more performant as a result):

PGDB[:profiles].import([:realname],res,commit_every:500)
PGDB[:profiles].multi_insert(res,commit_every:500)

edited Apr 27, 2012 at 18:50

answered Apr 27, 2012 at 18:45

Phrogz

304k115 gold badges669 silver badges758 bronze badges

1 Comment

the Tin Man Over a year ago

+1. Excellent examples of why I use Sequel, and why it's so nice.

Collectives™ on Stack Overflow

How do I insert an array into database table?

2 Answers 2

9 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

9 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related