Last active
January 15, 2021 10:53
-
-
Save PJZ9n/0e4ecda7713d8c25740522a3a00f4e83 to your computer and use it in GitHub Desktop.
RubyでSQLite3を使ってみる
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
require 'sqlite3' | |
DB_FILE_NAME = "test.db" | |
$db = SQLite3::Database.new(DB_FILE_NAME) | |
# 初期化(テーブル作成) | |
def init | |
sql = <<~'SQL' | |
CREATE TABLE IF NOT EXISTS users( | |
id PRIMARY KEY,--連番ID | |
username TEXT NOT NULL UNIQUE,--ユーザー名 文字列 重複だめ | |
foo REAL NOT NULL--なにかの値 float | |
) | |
SQL | |
$db.execute(sql) | |
end | |
# ユーザーデータの作成 | |
def create(username, foo) | |
sql = <<~'SQL' | |
INSERT INTO users( | |
username, | |
foo | |
)VALUES( | |
:username, | |
:foo | |
) | |
SQL | |
$db.execute(sql, :username => username, :foo => foo) | |
end | |
# ユーザーデータが存在しているか | |
def exist?(username) | |
sql = <<~'SQL' | |
SELECT foo FROM users WHERE username=:username LIMIT 1 | |
SQL | |
$db.execute(sql, :username => username) do |row| | |
return true | |
end | |
false | |
end | |
# ユーザーデータの更新(foo) | |
def update_foo(username, foo) | |
sql = <<~'SQL' | |
UPDATE users | |
SET foo=:foo | |
WHERE username=:username | |
SQL | |
$db.execute(sql, :username => username, :foo => foo) | |
end | |
# ユーザーデータの取得(foo) 存在しない場合はnil | |
def get_foo(username) | |
sql = <<~'SQL' | |
SELECT foo FROM users WHERE username=:username | |
SQL | |
$db.execute(sql, :username => username) do |row| | |
return row[0] | |
end | |
end | |
init | |
p exist? "aaaaa" # false | |
unless exist? "testuser" | |
create "testuser", 1.5 | |
end | |
p get_foo "testuser" # 1.5 | |
update_foo "testuser", 2.5 | |
p get_foo "testuser" # 2.5 | |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment