Python 操作SQlite库

最新推荐文章于 2025-01-20 08:30:00 发布

原创最新推荐文章于 2025-01-20 08:30:00 发布 · 1w 阅读

0 ·

CC 4.0 BY-SA版权

python 专栏收录该内容

11 篇文章

订阅专栏

本文详细介绍如何使用Python操作SQLite数据库，包括创建数据库和表、插入数据、查询数据、更新与删除等核心操作，为读者提供了一个从零开始的SQLite数据库操作实战教程。

（1）创建数据库

import sqlite3


# test.db is a file in the working directory.
conn = sqlite3.connect("test.db")


c = conn.cursor()


# create tables
c.execute('''CREATE TABLE category
      (id int primary key, sort int, name text)''')
c.execute('''CREATE TABLE book
      (id int primary key,
       sort int,
       name text,
       price real,
       category int,
       FOREIGN KEY (category) REFERENCES category(id))''')


# save the changes
conn.commit()


# close the connection with the database
conn.close()

（2）插入数据

import sqlite3

conn = sqlite3.connect("test.db")
c  = conn.cursor()

books = [(1, 1, 'Cook Recipe', 3.12, 1),
            (2, 3, 'Python Intro', 17.5, 2),
            (3, 2, 'OS Intro', 13.6, 2),
           ]

# execute "INSERT"
c.execute("INSERT INTO category VALUES (1, 1, 'kitchen')")

# using the placeholder
c.execute("INSERT INTO category VALUES (?, ?, ?)", [(2, 2, 'computer')])

# execute multiple commands
c.executemany('INSERT INTO book VALUES (?, ?, ?, ?, ?)', books)

conn.commit()
conn.close()

（3）查询数据

import sqlite3

conn = sqlite3.connect('test.db')
c = conn.cursor()

# retrieve one record
c.execute('SELECT name FROM category ORDER BY sort')
print(c.fetchone())
print(c.fetchone())

# retrieve all records as a list
c.execute('SELECT * FROM book WHERE book.category=1')
print(c.fetchall())

# iterate through the records
for row in c.execute('SELECT name, price FROM book ORDER BY sort'):
    print(row)

    print(row)

（4）更新与删除

conn = sqlite3.connect("test.db")
c = conn.cursor()

c.execute('UPDATE book SET price=? WHERE id=?',(1000, 1))
c.execute('DELETE FROM book WHERE id=2')

conn.commit()
conn.close()