Python 操作SQlite库

最新推荐文章于 2024-10-07 09:51:57 发布

林震南

最新推荐文章于 2024-10-07 09:51:57 发布

阅读量1w

点赞数

CC 4.0 BY-SA版权

分类专栏： python

本文链接：https://blog.csdn.net/csdnhsh/article/details/95767744

python 专栏收录该内容

11 篇文章

订阅专栏

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

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

（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()