Search code examples
mysqlrubypostgresqlpg

How do I insert an array into database table?

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?

Solution

  • 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) }