连接到 SQLite 数据库

最后修改于 2020 年 7 月 6 日

SQLite Ruby 教程的这一部分将向您展示如何连接到数据库，并对数据库执行一些简单操作。

开始之前

SQLite 附带了 sqlite3 命令行实用程序。它可用于对数据库发出 SQL 命令。现在我们将使用 sqlite3 命令行工具来创建一个新数据库。

$ sqlite3 test.db
SQLite version 3.6.22
Enter ".help" for instructions
Enter SQL statements terminated with a ";"

我们向 sqlite3 工具提供一个参数。 test.db 是数据库名称。它是我们磁盘上的一个文件。如果存在，则打开它。如果不存在，则创建它。

sqlite> .tables
sqlite> .exit
$ ls
test.db

.tables 命令给出了 test.db 数据库中的表列表。目前没有表。 .exit 命令终止 sqlite3 命令行工具的交互式会话。 ls Unix 命令显示当前工作目录的内容。我们可以看到 test.db 文件。所有数据都将存储在此单个文件中。

sqlite-ruby 接口用于使用 Ruby 语言与 SQLite 数据库交互。

$ sudo apt-get install libsqlite3-ruby

上面的命令在基于 Debian 的 Linux 系统上安装该模块。

第一步是创建一个 Database 对象。 Database 类封装了与 SQLite 数据库的单个连接。数据库对象使用 close 方法关闭。

SQLite3::Database.new dbname
SQLite3::Database.open dbname

new 方法创建一个新的 Database 对象，它打开给定的 dbname 文件。如果该文件不存在，则在可能的情况下创建它。默认情况下，新数据库将以数组形式返回结果行。 open 方法打开包含在给定文件中的数据库。

SQLite3::Database.new ":memory:"

如果为文件名提供一个特殊的字符串 :memory:，则可以创建一个内存数据库。

来源

在创建本教程时，我们参考了 sqlite-ruby.rubyforge.org 网站。

版本

在第一个代码示例中，我们将获取 SQLite 数据库的版本。

#!/usr/bin/ruby

require 'sqlite3'

begin
    
    db = SQLite3::Database.new ":memory:"
    puts db.get_first_value 'SELECT SQLITE_VERSION()'
    
rescue SQLite3::Exception => e 
    
    puts "Exception occurred"
    puts e
    
ensure
    db.close if db
end

在上面的 Ruby 脚本中，我们创建了一个新的内存数据库。我们执行了一个 SQL 语句，该语句返回 SQLite 数据库的版本。

require 'sqlite3'

我们使用 sqlite3 Ruby 模块连接到 SQLite 数据库。

db = SQLite3::Database.new ":memory:"

我们创建一个新的数据库对象。 Database 类封装了与 SQLite 数据库的单个连接。数据库在内存中创建。所以它不是永久性的。

puts db.get_first_value 'SELECT SQLITE_VERSION()'

我们调用 db 对象的 get_first_value 方法。它执行 SQL 语句并获取结果集第一行的第一个值。

rescue SQLite3::Exception => e 
    
    puts "Exception occurred"
    puts e

我们检查错误。这很重要，因为与数据库打交道容易出错。

ensure
    db.close if db
end

最后，我们释放资源。

$ ./version.rb
3.7.7

输出可能如上所示。

插入数据

我们将创建一个 Cars 表，并向其中插入几行。

#!/usr/bin/ruby

require 'sqlite3'

begin
    
    db = SQLite3::Database.open "test.db"
    db.execute "CREATE TABLE IF NOT EXISTS Cars(Id INTEGER PRIMARY KEY, 
        Name TEXT, Price INT)"
    db.execute "INSERT INTO Cars VALUES(1,'Audi',52642)"
    db.execute "INSERT INTO Cars VALUES(2,'Mercedes',57127)"
    db.execute "INSERT INTO Cars VALUES(3,'Skoda',9000)"
    db.execute "INSERT INTO Cars VALUES(4,'Volvo',29000)"
    db.execute "INSERT INTO Cars VALUES(5,'Bentley',350000)"
    db.execute "INSERT INTO Cars VALUES(6,'Citroen',21000)"
    db.execute "INSERT INTO Cars VALUES(7,'Hummer',41400)"
    db.execute "INSERT INTO Cars VALUES(8,'Volkswagen',21600)"
    
rescue SQLite3::Exception => e 
    
    puts "Exception occurred"
    puts e
    
ensure
    db.close if db
end

上面的脚本创建了一个 Cars 表，并向该表中插入 8 行。

db = SQLite3::Database.open "test.db"

我们连接到 test.db 数据库。

db.execute "CREATE TABLE IF NOT EXISTS Cars(Id INTEGER PRIMARY KEY, 
    Name TEXT, Price INT)"

execute 方法执行给定的 SQL 语句。如果不存在，则创建一个新的 Cars 表。

db.execute "INSERT INTO Cars VALUES(1,'Audi',52642)"
db.execute "INSERT INTO Cars VALUES(2,'Mercedes',57127)"

这两行将两辆车插入到表中。请注意，默认情况下，我们处于*自动提交模式*，对表的所有更改立即生效。

sqlite> .mode column  
sqlite> .headers on

我们使用 sqlite3 工具来验证写入的数据。首先，我们修改数据在控制台中的显示方式。我们使用列模式并打开表头。

sqlite> SELECT * FROM Cars;
Id          Name        Price     
----------  ----------  ----------
1           Audi        52642     
2           Mercedes    57127     
3           Skoda       9000      
4           Volvo       29000     
5           Bentley     350000    
6           Citroen     21000     
7           Hummer      41400     
8           Volkswagen  21600

这是我们已写入 Cars 表的数据。

最后插入的行 ID

有时我们需要确定最后插入行的 ID。我们使用 last_insert_row_id 方法来查找它。

#!/usr/bin/ruby

require 'sqlite3'

begin
    
    db = SQLite3::Database.new ":memory:"
    
    db.execute "CREATE TABLE Friends(Id INTEGER PRIMARY KEY, Name TEXT)"
    db.execute "INSERT INTO Friends(Name) VALUES ('Tom')"
    db.execute "INSERT INTO Friends(Name) VALUES ('Rebecca')"
    db.execute "INSERT INTO Friends(Name) VALUES ('Jim')"
    db.execute "INSERT INTO Friends(Name) VALUES ('Robert')"
    db.execute "INSERT INTO Friends(Name) VALUES ('Julian')"
    
    id = db.last_insert_row_id
    puts "The last id of the inserted row is #{id}"
    
rescue SQLite3::Exception => e 
    
    puts "Exception occurred"
    puts e
    
ensure
    db.close if db
end

我们在内存中创建一个 Friends 表。 Id 自动递增。

db.execute "CREATE TABLE Friends(Id INTEGER PRIMARY KEY, Name TEXT)"

在 SQLite 中，INTEGER PRIMARY KEY 列是自动递增的。还有一个 AUTOINCREMENT 关键字。当在 INTEGER PRIMARY KEY AUTOINCREMENT 中使用时，使用了稍有不同的 Id 创建算法。

db.execute "INSERT INTO Friends(Name) VALUES ('Tom')"
db.execute "INSERT INTO Friends(Name) VALUES ('Rebecca')"
db.execute "INSERT INTO Friends(Name) VALUES ('Jim')"
db.execute "INSERT INTO Friends(Name) VALUES ('Robert')"
db.execute "INSERT INTO Friends(Name) VALUES ('Julian')"

这五个 SQL 语句向 Friends 表中插入了五行。

id = db.last_insert_row_id

使用 last_insert_row_id 方法，我们获取最后插入的行 Id。

$ ./last_rowid.rb 
The last id of the inserted row is 5

我们看到脚本的输出。

获取数据

在本章的最后一个示例中，我们获取了一些数据。关于数据获取的更多信息将在“查询”章节中讨论。

#!/usr/bin/ruby

require 'sqlite3'

begin
    
    db = SQLite3::Database.open "test.db"
    
    stm = db.prepare "SELECT * FROM Cars LIMIT 5" 
    rs = stm.execute 
    
    rs.each do |row|
        puts row.join "\s"
    end
           
rescue SQLite3::Exception => e 
    
    puts "Exception occurred"
    puts e
    
ensure
    stm.close if stm
    db.close if db
end

在示例中，我们从 Cars 表中获取 5 行。

stm = db.prepare "SELECT * FROM Cars LIMIT 5" 
rs = stm.execute

我们使用 prepare 方法准备用于执行的 SQL 语句。该方法返回一个语句对象。然后使用 execute 方法执行 SQL 语句。它返回一个结果集。 ResutlSet 对象是查询返回的数据的简单游标。

rs.each do |row|
    puts row.join "\s"
end

使用 each 方法，我们遍历结果集中的数据。在每个循环中，它返回一行。该行是一个字段数组。这些字段用空格连接起来形成一行。

$ ./fetch.rb 
1 Audi 52642
2 Mercedes 57127
3 Skoda 9000
4 Volvo 29000
5 Bentley 350000

这是 fetch.rb 脚本的输出。

在本章的 SQLite Ruby 教程中，我们展示了如何建立与 SQLite 数据库的数据库连接。我们已经解释了对数据库执行一些基本工作的脚本。

电子书

连接到 SQLite 数据库

开始之前

来源

版本

插入数据

最后插入的行 ID

获取数据