• python内置库有哪些 重点 (Top highlight)Photo by mcmurryjulie on Pixabay 照片由mcmurryjulie在Pixabay上发布 您知道Python内置数据库吗? (Do You Know Python Has A Built-In Database?) Python内置库简介—...


    重点 (Top highlight)

    If you are a software developer, I believe you must know or even have used an extremely light-weighted database — SQLite. It has almost all the features you need as a relational database, but everything is saved in a single file. In the official site, here are some scenarios that you could use SQLite.

    如果您是软件开发人员,我相信您必须知道或什至使用过轻量级的数据库SQLite。 它具有关系数据库所需的几乎所有功能,但是所有内容都保存在一个文件中。 在官方站点中,这里有一些您可以使用SQLite的方案。

    • Embedded devices and IoT

    • Data Analysis

    • Data Transferring

    • File archive and/or data container

    • Internal or temporary databases

    • Stand-in for an enterprise database during demos or testing

    • Education, training and testing

    • Experimental SQL language extensions


    There are more reasons that you may want to use SQLite, please check out the documentation.


    Most importantly, SQLite is actually built-in as a Python library. In other words, you don’t need to install any server-side/client-side software, and you don’t need to keep something running as a service, as long as you imported the library in Python and start coding, then you have a relational database management system!

    最重要的是, SQLite实际上是作为Python库内置的 。 换句话说,您不需要安装任何服务器端/客户端软件,也不需要保持某些东西作为服务运行,只要您使用Python导入了该库并开始编码,那么您就可以有一个关系数据库管理系统!

    导入和使用 (Import and Usage)

    Image for post
    Photo by CopyrightFreePictures on Pixabay
    照片由CopyrightFreePictures· Pixabay上的免费照片

    When we say “built-in”, it means that you don’t even need to run pip install to acquire the library. Simply import it by:

    当我们说“内置”时,这意味着您甚至不需要运行pip install即可获取该库。 只需通过以下方式导入:

    import sqlite3 as sl

    创建与数据库的连接 (Create a Connection to DB)

    Don’t be bothered with the drivers, connection strings and so on. You can create an SQLite database and have a connection object as simple as:

    不要为驱动程序,连接字符串等烦恼。 您可以创建一个SQLite数据库并具有一个连接对象,该对象很简单:

    con = sl.connect('my-test.db')

    After we run this line of code, we have created the database and connected to it already. This is because the database we asked Python to connect to is not existing so that it automatically created an empty one. Otherwise, we can use exactly the same code to connect to an existing database.

    运行此行代码之后,我们已经创建数据库并已连接到该数据库。 这是因为我们要求Python连接的数据库不存在,因此它会自动创建一个空数据库。 否则,我们可以使用完全相同的代码连接到现有数据库。

    Image for post

    建立表格 (Create a Table)

    Then, let’s create a table.


    with con:
    name TEXT,
    age INTEGER

    In this USER table, we added three columns. As you can see, SQLite is indeed light-weight, but it supports all the basic features of a regular RDBMS should have, such as the data type, nullable, primary key and auto-increment.

    在此USER表中,我们添加了三列。 如您所见,SQLite确实是轻量级的,但是它支持常规RDBMS应具有的所有基本功能,例如数据类型,可为空,主键和自动递增。

    After running this code, we should have created a table already, although it outputs nothing.


    插入记录 (Insert Records)

    Let’s insert some records into the USER table we just created, which can also prove that we indeed created it.


    Suppose we want to insert multiple entries in one go. SQLite in Python can achieve this easily.

    假设我们要一次性插入多个条目。 Python中SQLite可以轻松实现这一目标。

    sql = 'INSERT INTO USER (id, name, age) values(?, ?, ?)'
    data = [
    (1, 'Alice', 21),
    (2, 'Bob', 22),
    (3, 'Chris', 23)

    We need to define the SQL statement with question marks ? as placeholder. Then, let’s create some sample data to be inserted. With the connection object, we can then insert these sample rows.

    我们需要用问号定义SQL语句? 作为占位符。 然后,让我们创建一些要插入的示例数据。 使用连接对象,然后可以插入这些示例行。

    with con:
    con.executemany(sql, data)

    It didn’t complain after we’ve run the code, so it was successful.


    查询表 (Query the Table)

    Now, it’s time to verify everything we have done in a tangible way. Let’s query the table to get the sample rows back.

    现在,是时候以切实的方式验证我们所做的一切。 让我们查询表以获取示例行。

    with con:
    data = con.execute("SELECT * FROM USER WHERE age <= 22")
    for row in data:
    Image for post

    You can see how simple it is.


    Also, even though SQLite is light-weighted, but as a widely-used database, most of the SQL clients software support to consume it.


    The one I use the most is DBeaver, let’s see how it looks like.


    从SQL Client(DBeaver)连接到SQLite DB (Connect to SQLite DB from SQL Client (DBeaver))

    Because I’m using Google Colab, so I’m going to download the my-test.db file to my local machine. In your case, if you run Python on your local machine, you can use your SQL client to connect directly to the databases file.

    因为我使用的是Google Colab,所以我将my-test.db文件下载到我的本地计算机上。 就您而言,如果您在本地计算机上运行Python,则可以使用SQL客户端直接连接到数据库文件。

    In DBeaver, create a new connection and select SQLite as DB type.


    Image for post

    Then, browse to the DB file.


    Image for post

    Now, you can run any SQL query on the database. It is nothing different from other regular relational databases.

    现在,您可以在数据库上运行任何SQL查询。 它与其他常规关系数据库没有什么不同。

    Image for post

    与Pandas无缝集成 (Seamless Integrate with Pandas)

    Image for post
    Photo by GraphicMama-team on Pixabay

    Do you think that’s all? No. In fact, as a built-in feature of Python, SQLite can seamlessly integrate with Pandas Data Frame.

    你认为就这些吗? 否。事实上,作为Python的内置功能,SQLite可以与Pandas Data Frame无缝集成。

    Let’s define a data frame.


    df_skill = pd.DataFrame({
    'user_id': [1,1,2,2,3,3,3],
    'skill': ['Network Security', 'Algorithm Development', 'Network Security', 'Java', 'Python', 'Data Science', 'Machine Learning']
    Image for post

    Then, we can simply call to_sql() method of the data frame to save it into the database.


    df_skill.to_sql('SKILL', con)

    That’s it! We even don’t need to create the table in advance, the column data types and length will be inferred. Of course, you can still define it beforehand if you want to.

    而已! 我们甚至不需要提前创建表,将推断出列数据类型和长度。 当然,如果需要,您仍然可以预先定义它。

    Then, let’s say we want to join the table USER and SKILL, and read the result into a Pandas data frame. It’s also seamless.

    然后,假设我们要连接表USERSKILL ,并将结果读入Pandas数据框。 它也是无缝的。

    df = pd.read_sql('''
    SELECT s.user_id, u.name, u.age, s.skill
    FROM USER u LEFT JOIN SKILL s ON u.id = s.user_id
    ''', con)
    Image for post

    Super cool! Let’s write the results to a new table called USER_SKILL.

    超酷! 让我们将结果写入一个名为USER_SKILL的新表中。

    df.to_sql('USER_SKILL', con)

    Then, we can also use our SQL client to retrieve the table.


    Image for post

    摘要 (Summary)

    Image for post
    Monoar_CGI_Artis on Monoar_CGI_Artis·Pixabay上的Pixabay免费图片

    Indeed, there are many surprises hidden in Python. They do not mean to be hidden, but just because there are too many out-of-box features existing in Python for one to discover all of them.

    确实,Python中隐藏了许多惊喜。 它们并不意味着被隐藏,只是因为Python中存在太多现成的功能,无法让人们发现所有这些功能。

    In this article, I have introduced how to use the Python built-in library sqlite3 to create and manipulate tables in an SQLite DB. Of course, it also supports updating and deleting but I think you would try it yourself after this.

    在本文中,我介绍了如何使用Python内置库sqlite3在SQLite DB中创建和操作表。 当然,它也支持更新和删除,但是我认为您可以在此之后自行尝试。

    Most importantly, we can easily read a table from an SQLite DB into a Pandas data frame, or vice versa. This allows us to even more easily to interact with our light-weight relational database.

    最重要的是,我们可以轻松地将SQLite数据库中的表读取到Pandas数据框中,反之亦然。 这使我们可以更轻松地与我们的轻量级关系数据库进行交互。

    You may notice that SQLite doesn’t have authentication, that’s it designed behaviour as everything needs to be light-weight. Go discovering more surprising features in Python, enjoy it!

    您可能会注意到SQLite没有身份验证,这是它设计的行为,因为所有内容都必须轻巧。 快去发现Python中更多令人惊讶的功能,尽情享受吧!

    All the code in this article can be found in my Google Colab Notebook.

    这篇文章中的所有代码都可以在我的Google Colab Notebook中找到。

    翻译自: https://towardsdatascience.com/do-you-know-python-has-a-built-in-database-d553989c87bd


  • from datetime import timezone, timedelta tz = timezone(timedelta(hours=1)) # 获取时区,hours表示一小时间隔,表示东一区。西一区可以使用-1 print(tz) ''' UTC+01:00 '''



1 2 3 4 5 ... 20
收藏数 96,789
精华内容 38,715


python 订阅