RUBY 和 SQLITE
来源:互联网 发布:程序员考试报名时间 编辑:程序博客网 时间:2024/06/05 08:41
1.创建数据库和获取单行或者是单列
require 'sqlite3'
db = SQLite3::Database.new( "test.db" )
rows = db.execute( "select * from test" )
2.数据库搜索 不同方式
require 'sqlite3'
db = SQLite3::Database.new( "test.db" )
db.execute( "select * from test" ) do |row|
...
end
require 'sqlite3'
db = SQLite3::Database.new( "test.db" )
columns, *rows = db.execute2( "select * from test" )
# or use a block:
columns = nil
db.execute2( "select * from test" ) do |row|
if columns.nil?
columns = row
else
# process row
end
end
3.仅仅需要结果的第一行
row = db.get_first_row( "select * from table" )count = db.get_first_value( "select count(*) from table" )
4.重复执行的操作
stmt = db.prepare( "select * from person" )
1000.times do
stmt.execute do |result|
...
end
end
stmt.close
# or, use a block
db.prepare( "select * from person" ) do |stmt|
1000.times do
stmt.execute do |result|
...
end
end
end
5.SQL中的占位符比如
:
??nnn
:word
db.execute( "select * from table where a = ? and b = ?",
"hello",
"world" ) db.execute( "select * from table where a = :name and b = :value",
"name" => "bob",
"value" => "priceless" ) stmt = db.prepare( "select * from table where a = :name and b = ?" )
stmt.bind_param( "name", "bob" )
stmt.bind_param( 1, "value" )
# or
stmt.bind_params( "value", "name" => "bob" ) 6.获取列信息 stmt = db.prepare( "select * from table where a = :name and b = ?" )
stmt.bind_param( "name", "bob" )
stmt.bind_param( 1, "value" )
# or
stmt.bind_params( "value", "name" => "bob" ) stmt = db.prepare( "select * from table where a = :name and b = ?" )
stmt.bind_param( "name", "bob" )
stmt.bind_param( 1, "value" )
# or
stmt.bind_params( "value", "name" => "bob" ) stmt = db.prepare( "select * from table" )
p stmt.columns
p stmt.types
7.获取列数据
db.results_as_hash = true
db.execute( "select * from table" ) do |row|
p row['column1']
p row['column2']
end require 'arrayfields'
...
db.execute( "select * from table" ) do |row|
p row[0] == row['column1']
p row[1] == row['column2']
end
8.I’d like the values from a query to be the correct types, instead of String.You can turn on “type translation” by setting Database#type_translation to true:
db.type_translation = true
db.execute( "select * from table" ) do |row|
p row
end
By doing this, each return value for each row will be translated to its correct type, based on its declared column type.You can even declare your own translation routines, if (for example) you are using an SQL type that is not handled by default: # assume "objects" table has the following schema:
# create table objects (
# name varchar2(20),
# thing object
# )
db.type_translation = true
db.translator.add_translator( "object" ) do |type, value|
db.decode( value )
end
h = { :one=>:two, "three"=>"four", 5=>6 }
dump = db.encode( h )
db.execute( "insert into objects values ( ?, ? )", "bob", dump )
obj = db.get_first_value( "select thing from objects where name='bob'" )
p obj == h
9.How do insert binary data into the database?
db.execute( "insert into foo ( ?, ? )",
SQLite3::Blob.new( "\0\1\2\3\4\5" ),
SQLite3::Blob.new( "a\0b\0c\0d ) )
10.How do I do a DDL (insert, update, delete) statement?
db.execute( "insert into table values ( ?, ? )", *bind_vars )
11.How do I execute multiple statements in a single string?
sql = <<SQL
create table the_table (
a varchar2(30),
b varchar2(30)
);
insert into the_table values ( 'one', 'two' );
insert into the_table values ( 'three', 'four' );
insert into the_table values ( 'five', 'six' );
SQL
db.execute_batch( sql )
12.How do I begin/end a transaction?
database.transaction do |db| db.execute( "insert into table values ( 'a', 'b', 'c' )" ) ... end db.transaction db.execute( "insert into table values ( 'a', 'b', 'c' )" ) db.commit
- RUBY 和 SQLITE
- ruby&sqlite的例子
- Ruby Sqlite学习心得
- 在Ruby on rails在使用SQLite
- ruby和ruby和oracle的关联
- Ruby和web开发
- Ruby入门教程和技巧
- java和ruby
- Ruby和web开发
- Ruby和Rails
- 介绍ruby和waitr
- php和ruby
- Ruby和Rails
- Ruby | Block和迭代器
- ruby 正则 $& 和 $'
- ruby和rails安装
- ruby和java
- ruby-module和class
- android事件处理
- cocos2d-x节点(b2CircleShape.h)API
- acm--杨辉三角
- iOS app 状态栏隐藏与颜色
- 从Java代码远程提交YARN MapReduce任务
- RUBY 和 SQLITE
- WPF使用DataGridComboBoxColumn完成绑定
- win7+ubuntu双系统安装方法
- quick-cocos2d-x下有关缓存图片的管理策略
- mysql 分页拦截器,类似hibernate方言
- Redis 代理服务Twemproxy
- ASP.NET MVC项目设置起始页后问题的修复
- Python subprocess shell 编程规范
- c语言中可以用fseek来定位文件指针的位置