44.md

# Python 数据库编程：SQLite（教程）

> 原文： [https://pythonspot.com/python-database-programming-sqlite-tutorial/](https://pythonspot.com/python-database-programming-sqlite-tutorial/)

在本教程中，您将学习如何在 Python 中使用 SQLite [数据库](https://pythonspot.com/python-database/)管理系统。 您将学习如何使用 SQLite，SQL 查询，RDBMS 以及更多有趣的东西！

**Related course:** [Master SQL Databases with Python](https://gumroad.com/l/JImAU)

## Pyton 数据库

![Python Database ](img/4c7e8cbef62872a3c49fc4085527514d.jpg)

Python Database.
Data is retrieved from a database system using the SQL language. Data is everywhere and software applications use that. Data is either in memory, files or databases.

Python has bindings for many database systems including [MySQL](https://pythonspot.com/mysql-with-python), [Postregsql](https://pythonspot.com/python-database-postgresql/), Oracle, Microsoft SQL Server and Maria DB.

One of these database management systems (DBMS) is called SQLite. SQLite was created in the year 2000 and is one of the many management systems in the database zoo.

SQL is a special-purpose programming language designed for managing data held in a [databases](https://pythonspot.com/python-database/). The language has been around since 1986 and is worth learning. The [is an old funny video about SQL](https://www.youtube.com/watch?v=5ycx9hFGHog)

## SQLite 的

![SQLite](img/b49bf74e6651a4fea2a00aefd48f3b71.jpg)

SQLite, a relational database management system. SQLite is the most widely deployed SQL database engine in the world. The source code for SQLite is in the public domain.

It is a self-contained, serverless, zero-configuration, transactional SQL database engine. The SQLite project is sponsored by Bloomberg and Mozilla.

## 安装 SQLite：

Use this command to install SQLite:

```py
$ sudo apt-get install sqlite

```

Verify if it is correctly installed. Copy this program and save it as test1.py

```py
#!/usr/bin/python
# -*- coding: utf-8 -*-

import sqlite3 as lite
import sys

con = None

try:
    con = lite.connect('test.db')
    cur = con.cursor()  
    cur.execute('SELECT SQLITE_VERSION()')
    data = cur.fetchone()
    print "SQLite version: %s" % data                
except lite.Error, e:   
    print "Error %s:" % e.args[0]
    sys.exit(1)
finally:    
    if con:
        con.close()

```

Execute with:

```py
$ python test1.py

```

It should output:

```py
SQLite version: 3.8.2

```

## What did the script above do?

The script connected to a new database called test.db with this line:

```py
con = lite.connect('test.db')

```

It then queries the database management system with the command

```py
SELECT SQLITE_VERSION()

```

which in turn returned its version number. That line is known as an SQL query.

## SQL 创建和插入

The script below will store data into a new database called user.db

```py
#!/usr/bin/python
# -*- coding: utf-8 -*-

import sqlite3 as lite
import sys

con = lite.connect('user.db')

with con:

    cur = con.cursor()    
    cur.execute("CREATE TABLE Users(Id INT, Name TEXT)")
    cur.execute("INSERT INTO Users VALUES(1,'Michelle')")
    cur.execute("INSERT INTO Users VALUES(2,'Sonya')")
    cur.execute("INSERT INTO Users VALUES(3,'Greg')")

```

SQLite is a database management system that uses tables. These tables can have relations with other tables: it’s called relational database management system or RDBMS. The table defines the structure of the data and can hold the data. A database can hold many different tables. The table gets created using the command:

```py
cur.execute("CREATE TABLE Users(Id INT, Name TEXT)")

```

We add records into the table with these commands:

```py
cur.execute("INSERT INTO Users VALUES(2,'Sonya')")
cur.execute("INSERT INTO Users VALUES(3,'Greg')")

```

The first value is the ID. The second value is the name. Once we run the script the data gets inserted into the database table Users:

![SQL Table](img/6331a77064535de9dc0a93e8b103f00d.jpg)

SQL Table

## SQLite 查询数据

We can explore the database using two methods: the command line and a graphical interface.

_ 从控制台：_ 要使用命令行进行浏览，请键入以下命令：

```py
sqlite3 user.db
.tables
SELECT * FROM Users;

```

这将在表 Users 中输出数据。

```py
sqlite> SELECT * FROM Users;
1|Michelle
2|Sonya
3|Greg

```

_ 从 GUI：_ 如果要使用 GUI，则有很多选择。 我个人选择了 sqllite-man，但还有[很多其他](https://stackoverflow.com/questions/835069/which-sqlite-administration-console-do-you-recommend)。 我们使用以下方法安装：

```py
sudo apt-get install sqliteman

```

我们启动应用程序 sqliteman。 gui 弹出。

![sqliteman](img/e57ee6ed25a87b23c3a3cfe92e1fa1fe.jpg)

sqliteman

按文件>打开>user.db。 似乎变化不大，不用担心，这只是用户界面。 左侧是一棵小树状视图，请按 Tables >用户。 现在将显示包括所有记录的完整表格。

![sqliteman](img/60354a5e7e39d04c97a1ee14b3b5db1b.jpg)

sqliteman

该 GUI 可用于修改表中的记录（数据）并添加新表。

## SQL 数据库查询语言

SQL has many commands to interact with the [database](https://pythonspot.com/python-database/). You can try the commands below from the command line or from the GUI:

```py
sqlite3 user.db 
SELECT * FROM Users;
SELECT count(*) FROM Users;
SELECT name FROM Users;
SELECT * FROM Users WHERE id = 2;
DELETE FROM Users WHERE id = 6;

```

我们可以在 Python 程序中使用这些查询：

```py
#!/usr/bin/python
# -*- coding: utf-8 -*-

import sqlite3 as lite
import sys

con = lite.connect('user.db')

with con:    

    cur = con.cursor()    
    cur.execute("SELECT * FROM Users")

    rows = cur.fetchall()

    for row in rows:
        print row

```

这将从数据库输出“用户”表中的所有数据：

```py
$ python get.py 
(1, u'Michelle')
(2, u'Sonya')
(3, u'Greg')

```

## 创建用户信息数据库


我们可以跨多个表构建数据。 这使我们的数据保持结构化，快速和有条理。 如果我们只有一个表来存储所有内容，那么很快就会陷入混乱。 我们将要做的是创建多个表并将它们组合使用。 我们创建两个表：

_ 位用户：_

![SQL Table](img/f713e77b9a67592c539974f0fd3c7bbe.jpg)

SQL Table

_ 职位：_

![SQL Table](img/e1cf4ef1a7f5301ee63ded4806461cb2.jpg)

SQL Table

要创建这些表，您可以在 GUI 中手动进行操作或使用以下脚本：

```py
# -*- coding: utf-8 -*-

import sqlite3 as lite
import sys

con = lite.connect('system.db')

with con:

    cur = con.cursor()    
    cur.execute("CREATE TABLE Users(Id INT, Name TEXT)")
    cur.execute("INSERT INTO Users VALUES(1,'Michelle')")
    cur.execute("INSERT INTO Users VALUES(2,'Howard')")
    cur.execute("INSERT INTO Users VALUES(3,'Greg')")

    cur.execute("CREATE TABLE Jobs(Id INT, Uid INT, Profession TEXT)")
    cur.execute("INSERT INTO Jobs VALUES(1,1,'Scientist')")
    cur.execute("INSERT INTO Jobs VALUES(2,2,'Marketeer')")
    cur.execute("INSERT INTO Jobs VALUES(3,3,'Developer')")

```

作业表有一个额外的参数 Uid。 我们使用它来连接 SQL 查询中的两个表：

```py
SELECT users.name, jobs.profession FROM jobs INNER JOIN users ON users.ID = jobs.uid

```

您可以将该 SQL 查询合并到 Python 脚本中：

```py
#!/usr/bin/python
# -*- coding: utf-8 -*-

import sqlite3 as lite
import sys

con = lite.connect('system.db')

with con:    

    cur = con.cursor()    
    cur.execute("SELECT users.name, jobs.profession FROM jobs INNER JOIN users ON users.ID = jobs.uid")

    rows = cur.fetchall()

    for row in rows:
        print row

```

它应该输出：

```py
$ python get2.py
(u'Michelle', u'Scientist')
(u'Howard', u'Marketeer')
(u'Greg', u'Developer')

```

您可能会喜欢：[数据库和数据分析](https://pythonspot.com/python-database/)