Python SQLite教程

最新推荐文章于 2024-08-28 09:00:00 发布

cunchi4221

最新推荐文章于 2024-08-28 09:00:00 发布

阅读量1.2k

点赞数 2

文章标签：数据库 python mysql java linux

原文链接：https://www.journaldev.com/20515/python-sqlite-tutorial

版权

If you’re looking for something with which you can use complete DB operations into your application without having to install any database server program such as MySQL, PostgreSQL, or Oracle, python sqlite3 module is for you.

如果您正在寻找可以在应用程序中使用完整的数据库操作而无需安装任何数据库服务器程序（例如MySQL，PostgreSQL或Oracle）的工具，则python sqlite3模块非常适合您。

Python SQLite (Python SQLite)

Python sqlite3 is an excellent module with which you can perform all possible DB operations with in-memory and persistent database in your applications.

Python sqlite3是一个出色的模块，您可以通过它使用应用程序中的内存和持久数据库执行所有可能的数据库操作。

This module implements the Python DB API interface to be a compliant solution for implementing SQL related operations in a program.

该模块将Python DB API接口实现为兼容的解决方案，用于在程序中实现与SQL相关的操作。

使用sqlite3模块 (Using sqlite3 module)

In this section, we will start using the sqlite3 module in our application so that we can create databases and tables inside it and perform various DB operations on it. Let’s get started.

在本节中，我们将开始在应用程序中使用sqlite3模块，以便我们可以在其中创建数据库和表并对其执行各种DB操作。让我们开始吧。

Python SQLite创建数据库 (Python SQLite Create Database)

When we talk about databases, we’re looking at a single file which will be stored on the file system and its access is managed by the module itself to prevent corruption when multiple users try to write to it.

当我们谈论数据库时，我们正在查看一个文件，该文件将存储在文件系统中，并且其访问由模块本身管理，以防止当多个用户尝试写入文件时损坏文件。

Here is a sample program which creates a new database before opening it for operations:

这是一个示例程序，可以在打开新数据库进行操作之前创建一个新数据库：

import os
import sqlite3

db_filename = 'journaldev.db'

db_exists = not os.path.exists(db_filename)
connection = sqlite3.connect(db_filename)

if db_exists:
    print('No schema exists.')
else:
    print('DB exists.')

connection.close()

We will run the program twice to check if it works correctly. Let’s see the output for this program:

我们将运行该程序两次，以检查其是否正常运行。让我们看一下该程序的输出：

Create new DB

创建新的数据库

As expected, second time we run the program, we see the output as
不出所料，第二次运行程序时，我们看到输出为 DB exists. DB exists 。

Python SQLite创建表 (Python SQLite Create Table)

To start working with the database, we must define a table schema on which we will write our further queries and perform operations. Here is the schema we will follow:

要开始使用数据库，我们必须定义一个表架构，在该架构上我们将编写进一步的查询并执行操作。这是我们将遵循的架构：

Python SQLite Table Schema

Python SQLite表架构

For the same schema, we will be writing related SQL Query next and these queries will be saved in book_schema.sql:

对于相同的架构，我们接下来将编写相关SQL查询，并将这些查询保存在book_schema.sql ：

CREATE TABLE book (
    name        text primary key,
    topic       text,
    published   date
);

CREATE TABLE chapter (
    id           number primary key autoincrement not null,
    name         text,
    day_effort   integer,
    book         text not null references book(name)
);

Now let us use the connect() function to connect to the database and insert some initial data using the executescript() function:

现在，让我们使用connect()函数连接到数据库，并使用executescript()函数插入一些初始数据：

import os
import sqlite3

db_filename = 'journaldev.db'
schema_filename = 'book_schema.sql'

db_exists = not os.path.exists(db_filename)

with sqlite3.connect(db_filename) as conn:
    if db_exists:
        print('Creating schema')
        with open(schema_filename, 'rt') as file:
            schema = file.read()
        conn.executescript(schema)

        print('Inserting initial data')

        conn.executescript("""
        insert into book (name, topic, published)
        values ('JournalDev', 'Java', '2011-01-01');

        insert into chapter (name, day_effort, book)
        values ('Java XML', 2,'JournalDev');

        insert into chapter (name, day_effort, book)
        values ('Java Generics', 1, 'JournalDev');

        insert into chapter (name, day_effort, book)
        values ('Java Reflection', 3, 'JournalDev');
        """)
    else:
        print('DB already exists.')

When we execute the program and check what all data is present in chapter table, we will see the following output:

当我们执行程序并检查章节表中所有数据时，我们将看到以下输出：

DB with initial data

具有初始数据的数据库

See how I was able to request the db file directory from the command line. We will be querying data from sqlite3 module itself in next section.
查看如何从命令行请求db文件目录。我们将在下一节中从sqlite3模块本身查询数据。

Python SQLite游标选择 (Python SQLite Cursor Select)

Now, we will retrieve data in our script by using a Cursor to fetch all chapters which fulfil some criteria:

现在，我们将通过使用游标获取满足某些条件的所有章节来检索脚本中的数据：

import sqlite3

db_filename = 'journaldev.db'

with sqlite3.connect(db_filename) as conn:
    cursor = conn.cursor()

    cursor.execute("""
    select id, name, day_effort, book from chapter
    where book = 'JournalDev'
    """)

    for row in cursor.fetchall():
        id, name, day_effort, book = row
        print('{:2d} ({}) {:2d} ({})'.format(
            id, name, day_effort, book))

Let’s see the output for this program:

让我们看一下该程序的输出：

Fetch data from DB

从数据库获取数据

This was a simple example of fetching data from a table where one column matches a specific value.

这是一个简单的示例，该数据是从表中获取数据的，其中一列与特定值匹配。

获取表的元数据 (Getting Metadata of Table)

In our programs, it is also important to get metadata for a table for documentation purposes and much more:

在我们的程序中，获取表的元数据用于文档编制以及其他目的也很重要：

import sqlite3

db_filename = 'journaldev.db'

with sqlite3.connect(db_filename) as connection:
    cursor = connection.cursor()

    cursor.execute("""
    select * from chapter where book = 'JournalDev'
    """)

    print('Chapter table has these columns:')
    for column_info in cursor.description:
        print(column_info)

Let’s see the output for this program:

让我们看一下该程序的输出：

Metadata of a Table

表的元数据

Due to the reason while creating schema, we didn’t provided the column anything apart from their names, most of the values are None.
由于创建架构时的原因，我们没有为列提供名称以外的任何内容，大多数值均为None。

使用命名参数 (Using Named Parameters)

With named parameters, we can pass arguments to our scripts and hence, the SQL Queries we write in our programs. Using Named Parameters is very easy, let’s take a look at how we can do this:

使用命名参数，我们可以将参数传递给脚本，因此可以传递给我们在程序中编写SQL查询。使用命名参数非常简单，让我们看一下如何做到这一点：

import sqlite3
import sys

db_filename = 'journaldev.db'
book_name = sys.argv[1]

with sqlite3.connect(db_filename) as conn:
    cursor = conn.cursor()

    query = """
    select id, name, day_effort, book from chapter
    where book = :book_name
    """

    cursor.execute(query, {'book_name': book_name})
    for row in cursor.fetchall():
        id, name, day_effort, book = row
        print('{:2d} ({}) {:2d} ({})'.format(
            id, name, day_effort, book))

Let’s see the output for this program:

让我们看一下该程序的输出：

Passing named parameter

传递命名参数

See how easy it was to pass a named parameter and substitute it in the query right before we execute it.
看看在执行命名参数之前将其传递到查询中是多么容易。

Python SQLite3事务管理 (Python SQLite3 Transaction Management)

Well, Transactions are a feature for which relational databases are known for. The sqlite3 module is completely capable of managing the internal state of a transaction, the only thing we need to do is letting it know that a Transaction is going to happen.

好吧，事务是关系数据库众所周知的功能。 sqlite3模块完全能够管理事务的内部状态，我们唯一需要做的就是让它知道事务即将发生。

Here is a sample program which describes how we write transactions in our program by explicitly calling the commit() function:

这是一个示例程序，描述了我们如何通过显式调用commit()函数在程序中编写事务：

import sqlite3

db_filename = 'journaldev.db'

def show_books(conn):
    cursor = conn.cursor()
    cursor.execute('select name, topic from book')
    for name, topic in cursor.fetchall():
        print('  ', name)


with sqlite3.connect(db_filename) as conn1:
    print('Before changes:')
    show_books(conn1)

    # Insert in one cursor
    cursor1 = conn1.cursor()
    cursor1.execute("""
    insert into book (name, topic, published)
    values ('Welcome Python', 'Python', '2013-01-01')
    """)

    print('\nAfter changes in conn1:')
    show_books(conn1)

    # Select from another connection, without committing first
    print('\nBefore commit:')
    with sqlite3.connect(db_filename) as conn2:
        show_books(conn2)

    # Commit then select from another connection
    conn1.commit()
    print('\nAfter commit:')
    with sqlite3.connect(db_filename) as conn3:
        show_books(conn3)

Let’s see the output for this program:

让我们看一下该程序的输出：

Running Transactions

进行交易

When the show_books(...) function is called before conn1 has been committed, the result depends on which connection is being used. As the changes were made from the conn1, it sees the made changes but conn2 doesn’t. Once we committed all the changes, all connections were able to see the made changes, including the conn3.

在提交conn1之前调用show_books(...)函数时，结果取决于所使用的连接。由于更改是从conn1 ，因此可以看到已进行的更改，但conn2没有。提交所有更改后，所有连接都可以查看所做的更改，包括conn3 。

结论 (Conclusion)

In this lesson, we studied the basics of the sqlite3 module in Python and committed transactions as well. When your program wants to work with some relational data, sqlite3 module provides an easy way to deal with data and obtain results across the life of the program as well.

在本课程中，我们研究了Python中sqlite3模块的基础知识以及承诺事务。当您的程序要使用某些关系数据时，sqlite3模块提供了一种简便的方法来处理数据并在程序的整个生命周期内获取结果。