SQLite：python built-in数据库

数据库database基础数据结构：

• Table：relation
• Row：tuple
• Column：attribute

创建database：

import sqlite3

conn = sqlite3.connect('music.sqlite') # create memory space/link to space(file) if exist
cur = conn.cursor() # create cursor, like open() when dealing files

cur.execute("""DROP TABLE IF EXISTS Tracks""") # execute commands
# The first SQL command removes the Tracks table from the database if it
# exists. This pattern is simply to allow us to run the same program to create
# the Tracks table over and over again without causing an error.

cur.execute("""CREATE TABLE Tracks (
				title TEXT,
				plays INTEGER
			)""") # create table contents

conn.commit()

conn.close()

database有自己独立的的language和语法也就是SQL，SQL命令的构成如下：

• SQL keywords in uppercase
• the parts of the command that we are adding (such as the table and column names) will be shown in lowercase.
• DROP TABLE：完全删除table
• The cursor is something we can loop through in a for statement.

基本语法

INSERT

• hard code，直接编辑value的内容by typing

cur.execute("INSERT INTO Tracks VALUES ('My Way', 15)")

• tuple：reference variable by using placeholder ?，后接reference的内容（以tuple的形式），可以是variable name

cur.execute('INSERT INTO Tracks VALUES (?, ?)', ('Thunderstruck', 20))

• dict：reference variable by using dict key placeholder :key_name，后接reference内容（以dict形式），可以是variable name

cur.execute('INSERT INTO Tracks VALUES (:title, :plays)', {'title':'Thunderstruck', 'plays':20})

SELECT with WHERE

• hard code，直接编辑value的内容by typing

cur.execute("SELECT * FROM Tracks WHERE title = 'My Way' ")
print(cur.fetchall())

• tuple：reference variable by using placeholder ?，后接reference的内容（以tuple的形式），可以是variable name

cur.execute("SELECT * FROM Tracks WHERE title = ?", ('My Way',))
print(cur.fetchall())

• dict：reference variable by using dict key placeholder :key_name，后接reference内容（以dict形式），可以是variable name

cur.execute("SELECT * FROM Tracks WHERE title = :title", {'title': 'My Way'})
print(cur.fetchall())

UPDATE

同理同上

cur.execute("UPDATE Tracks SET title = :title", {'title': 'Galway Girl'})
print(cur.fetchall())

DELETE

同理同上

cur.execute("DELETE FROM Tracks WHERE title = :title", {'title': 'Galway Girl'})
print(cur.fetchall())