Geek asks and answers

How to insert an array into a 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 a variable resand skip it to insert it into my Postgres database?

+3
source share
2 answers

The MySQL method querygives you an instance Mysql::Result, so you can use eachto 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 it each_hashmight be easier to work, which causes a block using Hash, which maps the column names to their values.

To insert data into PostgreSQL, use exec:

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

, , , 1999 PHP.

prepare 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 ])
# ...

prepare , SQL .

, :

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) }
+3

Sequel, import:

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

& hellip; 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)

( ):

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

More articles:


All Articles