Rails的csv格式原始查询,将通过控制器返回 [英] Rails raw query for csv format, to be returned via controller
问题描述
我使用活动记录获取故事,然后生成CSV,这是在rails cast中完成的标准方式。但是我有很多行,而且要花几分钟。我想如果我可以让posgresql进行csv渲染,那么我可以节省一些时间。
这里是我现在拥有的内容:
query =使用CSV标题将故事复制到标准输出;
结果= ActiveRecord :: Base.connection.execute(query);
但是此查询的结果为空:
=> #< PG ::结果:0x00000006ea0488 @connection =#< PG :: Connection:0x00000006c62fb8 @ socket_io = nil,@ notice_receiver = nil,@ notice_processor = nil>>
2.0.0-p247:053> result.count
=> 0
更好的了解方式:
2.0.0-p247:059> result.to_json
=> []
我怀疑我的控制器看起来像这样:
format.csv {send_data raw_results}
这适用于普通查询,我只是无法弄清楚SQL语法是否可以将CSV结果返回到rails。
UPDATE >
将CSV导出时间从120000毫秒降低到290毫秒
我的模型:
def self.to_csv(story_ids)
csv = []
conn = ActiveRecord :: Base.connection .raw_connection
conn.copy_data( COPY(选择*从故事WHERE story.id IN(#{story_ids.join(',')}))到(标准CSV,HEADER TRUE,FORCE_QUOTE *,ESCAPE E'\');)做
,而row = conn.get_copy_data
csv.push(row)
结束
结束
csv.join( \r\n)
end
我的控制器:
send_data Story.to_c sv(Story.order(:created_at).pluck(:id))
AFAIK,您需要使用 copy_data
在下面的PostgreSQL数据库连接上的方法:
-(对象)copy_data(sql)
呼叫序列:
conn.copy_data(sql){ | sql_result | ...}-> PG :: Result
执行复制过程以将[sic]数据传输到服务器或从服务器传输。 / p>
这将通过
#exec
发出SQLCOPY
命令。对此的响应(如果命令中没有错误)是一个PG :: Result
对象,该对象传递给块,带有状态代码PGRES_COPY_OUT或PGRES_COPY_IN(取决于指定的复印方向)。然后,应用程序应使用#put_copy_data
或#get_copy_data
接收或传输数据行,并应在完成后从块中返回。
还有一个例子:
conn.copy_data将my_table复制到STDOUT CSV执行
,而row = conn.get_copy_data
p行
结束
结束
ActiveRecord的原始数据库连接包装器不知道 copy_data
是什么,但是您可以使用 raw_connection
取消包装:
conn = ActiveRecord :: Base.connection.raw_connection
csv = []
conn.copy_data('将故事复制到具有csv标头的stdout')进行
,而row = conn.get_copy_data
csv.push(row)
结束
结束
那会让你机智在 csv
中创建一个CSV字符串数组(每个数组条目一个CSV行),您可以 csv.join( \r\n )
获取最终的CSV数据。
I was using active record to get my stories and then generate a CSV, the standard way as done in the rails cast. But I have a lot of rows and it takes minutes. I think if I could get posgresql to do the csv rendering, then I could save some time.
Heres what I have right now:
query = "COPY stories TO STDOUT WITH CSV HEADER;"
results = ActiveRecord::Base.connection.execute(query);
But the results are empty for this query:
=> #<PG::Result:0x00000006ea0488 @connection=#<PG::Connection:0x00000006c62fb8 @socket_io=nil, @notice_receiver=nil, @notice_processor=nil>>
2.0.0-p247 :053 > result.count
=> 0
A better way of knowing:
2.0.0-p247 :059 > result.to_json
=> "[]"
I suspect my controller will look something like this:
format.csv { send_data raw_results }
This works for normal queries, I just can't figure out the SQL syntax to have the CSV results returned to rails.
UPDATE
Got the CSV export from 120000 msec down to 290 msec
My model:
def self.to_csv(story_ids)
csv = []
conn = ActiveRecord::Base.connection.raw_connection
conn.copy_data("COPY (SELECT * FROM stories WHERE stories.id IN (#{story_ids.join(',')})) TO STDOUT WITH (FORMAT CSV, HEADER TRUE, FORCE_QUOTE *, ESCAPE E'\\\\');") do
while row = conn.get_copy_data
csv.push(row)
end
end
csv.join("\r\n")
end
My controller:
send_data Story.to_csv(Story.order(:created_at).pluck(:id))
AFAIK you need to use the copy_data
method on the underlying PostgreSQL database connection for this:
- (Object) copy_data(sql)
call-seq:
conn.copy_data( sql ) {|sql_result| ... } -> PG::Result
Execute a copy process for transferring [sic] data to or from the server.
This issues the SQL
COPY
command via#exec
. The response to this (if there is no error in the command) is aPG::Result
object that is passed to the block, bearing a status code of PGRES_COPY_OUT or PGRES_COPY_IN (depending on the specified copy direction). The application should then use#put_copy_data
or#get_copy_data
to receive or transmit data rows and should return from the block when finished.
And there's even an example:
conn.copy_data "COPY my_table TO STDOUT CSV" do
while row=conn.get_copy_data
p row
end
end
ActiveRecord's wrapper for the raw database connection doesn't know what copy_data
is but you can use raw_connection
to unwrap it:
conn = ActiveRecord::Base.connection.raw_connection
csv = [ ]
conn.copy_data('copy stories to stdout with csv header') do
while row = conn.get_copy_data
csv.push(row)
end
end
That would leave you with an array of CSV strings in csv
(one CSV row per array entry) and you could csv.join("\r\n")
to get the final CSV data.
这篇关于Rails的csv格式原始查询,将通过控制器返回的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!