Python中使用数据库SQLite和MySQL

一.数据库SQLite

1 建立数据库与建立表

直接来看例子：

import sqlite3

conn = sqlite3.connect("test.db");
c = conn.cursor();
c.execute("CREATE TABLE IF NOT EXISTS students (sid INTEGER PRIMARY KEY, name TEXT)");
conn.commit();
conn.close();

2 插入、删除、修改
 

import sqlite3

conn = sqlite3.connect(":memory:");
c = conn.cursor();
c.execute("CREATE TABLE students ("
          "sid INTEGER PRIMARY KEY,"
          " name TEXT)");
conn.commit();

c.execute("INSERT INTO students VALUES(?, ?)", (1, "Alice"));
c.execute("INSERT INTO students VALUES(?, ?)", (2, "Bob"));
c.execute("INSERT INTO students VALUES(?, ?)", (3, "Peter"));

c.execute("DELETE FROM students WHERE sid = ?", (1, ));
c.execute("UPDATE students SET name = ? WHERE sid = ?", ("Mark4", 3));

conn.commit();


# 查询数据的SQL语句
SQL = ''' SELECT * FROM STUDENTs;'''
# 查询数据
c.execute(SQL)
for row in c.fetchall():
 print(row)

 # c.execute("SELECT * FROM students");
 # print c.fetchall();


conn.close();

3.把上面的操作写成函数形式：

import sqlite3
conn = sqlite3.connect(":memory:");

def initialize(conn):
    c = conn.cursor();
    c.execute("CREATE TABLE students (sid INTEGER PRIMARY KEY, name TEXT)");
    conn.commit();

def insert(conn, sid, name):
    c = conn.cursor();
    t = (sid, name);
    c.execute("INSERT INTO students VALUES (?, ?)", t);
    conn.commit();

def delete(conn, sid):
    c = conn.cursor();
    t = (sid, );
    c.execute("DELETE FROM students WHERE sid = ?", t);
    conn.commit();

def update(conn, sid, name):
    c = conn.cursor();
    t = (name, sid);
    c.execute("UPDATE students SET name = ? WHERE sid = ?", t);
    conn.commit();

def display(conn):
    c = conn.cursor();
    c.execute("SELECT * FROM students");
    print(c.fetchall());

db_name = ":memory:";
conn = sqlite3.connect(db_name);

initialize(conn);

print("Insert 3 records.")
insert(conn, 1, "Alice");
insert(conn, 2, "Bob");
insert(conn, 3, "Peter");
display(conn);

print("Delete the record where sid = 1.")
delete(conn, 1);
display(conn);

print("Update the record where sid = 3.")
update(conn, 3, "Mark");
display(conn);

conn.close();