python调用数据库sql

基本命令

打开或创建数据库，返回连接conn：

conn = sqlite3 .connect(filename)

执行一个SQL命令：

conn.executescript(sql)

返回一个光标：

cursor = conn.cursor()

执行一条SQL查询命令，返回数据行：

cursor.execute(sql)

返回数据行列表（全部拿来）：

rows = cursor.fetchall()

import os
import sqlite3
db_filename='mydatabase.db'
#
#if DB exists - delete it
#
exists = os.path.exists(db_filename)
if exists:
    os.unlink(db_filename)
#
#connect to DB (create it if it doesn't exist)
#
conn = sqlite3.connect(db_filename)
#
#create a table
#
schema="""create table person (
id integer primary key autoincrement not null,
name text not null,
dob date,
nationality text,
gender text)
"""
conn.executescript(schema)
#
# create some data
#
people="""insert into person (name, dob,nationality,gender)
values ('Fred Bloggs', '1965-12-25','British','Male');
insert into person (name, dob,nationality,gender)
values ('Santa Claus', '968-01-01','Lap','Male');
insert into person (name, dob,nationality,gender)
values ('Tooth Fairy', '1931-03-31','American','Female');
"""
conn.executescript(people)


#
#attempt to insert a person with no name
#
try:
    dupe="insert into person (name, dob,nationality,gender) \
    values ('ni ma','1941-03-31','American','Female');"
    conn.executescript(dupe)
except Exception as e:
    print('Cannot insert record',e.__class__.__name__)
    
#
#execute a query
#
cursor = conn.cursor()
cursor.execute("select id, name, dob,nationality,gender from person")
for row in cursor.fetchall():
    id, name, dob,nationality,gender = row
    print("%3d %15s %12s %10s %6s" % (id, name, dob,nationality,gender))
    

注意，cursor单次有效。执行查询命令，必须有selecd id。

修改已有的数据库：

import sqlite3

db_filename = 'mydatabase.db'
conn = sqlite3.connect(db_filename)

cursor = conn.cursor()
query = "update person set nationality = 'chinese' where id = 2"
cursor.execute(query)

cursor.execute("select id, name, dob,nationality,gender from person")
for row in cursor.fetchall():
    id, name, dob,nationality,gender = row
    print("%3d %15s %12s %10s %6s" % (id, name, dob,nationality,gender))

输出

更多数学原理小文请关注公众号：未名方略