0
2

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?

More than 1 year has passed since last update.

SQLコマンドまとめ

Posted at

Learn Sqlite Database With Python The Fast and Easy Way!を受けたので、そこで学んだコマンドを記事にまとめます。

テーブルを作る

インポートする
import sqlite3

#一時的なデータベースを作る
conn = sqlite3.connect(':memory:')

#名前のついたデータベースを作る
conn = sqlite3.connect('customer.db')

#cursorを作る
c = conn.cursor()

#テーブルを作る
c.execute("""CREATE TABLE customers (
first_name text,
last_name text,
email text
)""")

# Commit our command
conn.commit()

# Close our connection
conn.close()


データをリスト形式で挿入する

#データのリストを作成する
many_customers = [('Wes', 'Brown', 'wes@brown.com'),
 ('Steph', 'Kuewa', 'steph@kuewa.com'),
 ('Dan', 'Pas', 'dan@pas.com')]
#Insert into a database
c.executemany("INSERT INTO customers VALUES (?, ?, ?)", many_customers)

クエリを書き込む

SELECT

c.execute("SELECT * FROM customers")

fetchで抽出

c.fetchone()
c.fetchmany(3)
items = c.fetchall()
#リストに格納することもできる

fetchはリスト形式で出力されるので添字やfor文を使える

c.fetchone()[0]

for item in items:
    print(item[0] + " " + item[1] + "\t" + item[2])

列番号をつけて出力

c.execute("SELECT rowid, * FROM customers")

WHERE

WHERE以下に条件式をつけることができる

c.execute("SELECT * FROM customers WHERE email LIKE '%codemy.com'")
c.execute("SELECT * FROM customers WHERE age >= 20")

UPDATE

c.execute("""UPDATE customers SET first_name = 'Mary'
WHERE rowid = 4
""")

DELETE(要素を削除)

c.execute("DELETE from customers WHERE rowid = 6")

ORDER BY

c.execute("SELECT rowid, * FROM customers ORDER BY last_name DESC")

DESC は降順
ASCは昇順

AND, OR

c.execute("SELECT rowid, * FROM customers WHERE last_name LIKE 'Br%' OR rowid = 3")

LIMIT

抽出する要素の個数を制限する。おそらく記述の順番が大事?

c.execute("SELECT rowid, * FROM customers ORDER BY rowid DESC LIMIT 2")

Drop Table

c.execute("DROP TABLE customers")
0
2
0

Register as a new user and use Qiita more conveniently

  1. You get articles that match your needs
  2. You can efficiently read back useful information
  3. You can use dark theme
What you can do with signing up
0
2

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?