如何在 Python 中使用 SQLite

关系型数据库管理系统（RDBMS）非常流行，也是应用开发中不可或缺的一部分。现有许多不同的 RDBMS，例如 MySQL、PostgreSQL、IBM DB2、Oracle 11g 等。SQLite 就是其中之一。SQLite 应用广泛，并深受开发者喜爱，原因包括：

极其轻量（不超过 500 KB）
无服务器（serverless），无需单独部署服务器即可使用
无需复杂配置
完全具备事务与并发特性
等等……

不过，SQLite 也存在一些局限。例如，它不支持 RIGHT OUTER JOIN 和 FULL OUTER JOIN 等连接。但总体而言，优势远大于限制。在本教程中，您将学习在 Python 中使用 SQLite，以下为教程内容概览：

SQLite 的安装与设置
在 SQLite 中创建数据库与数据表
将 .csv 文件导入 SQLite 数据库
在 Python 中使用 SQLite

注意：本教程假设您已熟悉 SQL（任一 RDBMS 均可）与 Python（3）的基础知识。如果您想要温习，以下资源或许有帮助：

当您对 SQL 与 Python 感到熟悉后，欢迎回来从中断处继续学习。

安装与设置

安装和设置 SQLite 只需几分钟。您可以通过命令行工具使用 SQLite，但也有基于 GUI 的工具，能提供更友好的图形界面。在本教程中，您将使用 DB Browser for SQLite。首先，请根据您的操作系统到此处下载该工具。

在 Windows 7 平台上，DB Browser for SQLite 的界面如下所示：

创建数据库与数据表

准备好 DB Browser 工具后，您可以创建一个新的 SQLite 数据库以继续。为此，点击New Database 选项卡：

点击 New Database 后，系统会提示您输入数据库名称。输入任意您喜欢的名称并继续。将会立即创建一个同名的空数据库，随后会提示您在该数据库下创建数据表。您可以先跳过创建表的步骤，稍后我们会进行。

要创建表，您需要先确定表的模式（schema）。为便于理解，我们先创建一个名为 consumers 的简单表，包含以下字段与数据类型：

consumer_id（整数）
consumer_full_name（字符串）（不可为 null）
consumer_email（字符串）（不可为 null）
consumer_grade（字符）（不可为 null）

注意：如果您熟悉数据库模式设计，应该会想到 consumer_id 是该表的主键（因此以下划线表示）。

要创建表，点击Create Table 选项卡，系统会提示您输入要创建的数据表的详细信息：

仔细观察上图，您会看到其中包含了您希望在 consumers 表中设置的全部细节。您还可以看到创建该表的对应 SQL。DB Browser 工具能高效完成这些操作。完成后点击 OK 按钮，稍早创建的数据库下应会出现 consumers 表：

出现 sqlite_sequence 表是因为如果您将 consumer_id 字段设为自增，SQLite 会创建单独的表来维护序列。您也可以前往 Execute SQL 区域执行其他 SQL 查询。欢迎尝试执行一些您喜欢的 SQL 语句。

在用 Python 与 SQLite 数据库交互之前，我们先来看如何将 .csv 文件导入 SQLite 数据库并用于分析。

将 .csv 文件导入 SQLite 数据库

要将 .csv 文件导入您创建的数据库，按以下路径操作：File -> Import -> Table from CSV file。您可以使用任意 .csv 文件。我们的示例文件包含全球各国的相关信息。选择该文件后，会看到如下对话框：

在这里，DB Browser 允许您指定多项内容，包括表名。请确保勾选 Column names in the first line 选项，以便 SQLite 能自动提取列名。完成设置后点击 OK。

在我们的示例中，您应该可以看到新增的数据表条目：

您可以随意执行一些 select 查询，检查表是否正确导入。

在 Python 中使用 SQLite

现在，您已有可用的数据库与数据表。若要在 Python 中与 SQLite 数据库交互，您需要使用 sqlite3 模块，它包含在 Anaconda 发行版中。

接下来，使用 sqlite3 提供的 connect() 方法连接您创建的数据库。该方法返回一个 Connection 对象。将数据库路径传入 connect 方法。数据库通常以 .db 为扩展名。

import sqlite3

conn = sqlite3.connect('tutorial.db')
技术支持

获得数据库的 Connection 后，您可以创建一个 Cursor 对象，并调用其 execute() 方法执行 SQL 命令。

cur = conn.cursor()
cur.execute('SELECT * from countries')
技术支持

<sqlite3.Cursor at 0x398ace0>
技术支持

执行 SELECT 语句后，您可以：

将游标对象 cur 当作迭代器，调用 fetchone() 方法显示一行；或
调用 fetchall() 方法显示多行组成的列表

我们分别试一下。

print(cur.fetchone())
技术支持

('AFG', 'Afghanistan', 'Asia', 'Southern and Central Asia', '1919', 'Afganistan/Afqanestan', 'Islamic Emirate', 'Kabul')
技术支持

print(cur.fetchall())
技术支持

[('NLD', 'Netherlands', 'Europe', 'Western Europe', '1581', 'Nederland', 'Constitutional Monarchy', 'Amsterdam'), ('ALB', 'Albania', 'Europe', 'Southern Europe', '1912', 'Shqiperia', 'Republic', 'Tirane'), ('DZA', 'Algeria', 'Africa', 'Northern Africa', '1962', 'Al-Jaza\x92ir/Algerie', 'Republic', 'Algiers'), ('ASM', 'American Samoa', 'Oceania', 'Polynesia', None, 'Amerika Samoa', 'US Territory', 'Pago Pago'), ('AND', 'Andorra', 'Europe', 'Southern Europe', '1278', 'Andorra', 'Parliamentary Coprincipality', 'Andorra la Vella'), ('AGO', 'Angola', 'Africa', 'Central Africa', '1975', 'Angola', 'Republic', 'Luanda'), ('ATG', 'Antigua and Barbuda', 'North America', 'Caribbean', '1981', 'Antigua and Barbuda', 'Constitutional Monarchy', "Saint John's"), ('ARE', 'United Arab Emirates', 'Asia', 'Middle East', '1971', 'Al-Imarat al-´Arabiya al-Muttahida', 'Emirate Federation', 'Abu Dhabi'), ('ARG', 'Argentina', 'South America', 'South America', '1816', 'Argentina', 'Federal Republic', 'Buenos Aires'), ('ARM', 'Armenia', 'Asia', 'Middle East', '1991', 'Hajastan', 'Republic', 'Yerevan'), ('ABW', 'Aruba', 'North America', 'Caribbean', None, 'Aruba', 'Nonmetropolitan Territory of The Netherlands', 'Oranjestad'), ('AUS', 'Australia', 'Oceania', 'Australia and New Zealand', '1901', 'Australia', 'Constitutional Monarchy, Federation', 'Canberra'), ('AZE', 'Azerbaijan', 'Asia', 'Middle East', '1991', 'Azarbaycan', 'Federal Republic', 'Baku'), ('BHS', 'Bahamas', 'North America', 'Caribbean', '1973', 'The Bahamas', 'Constitutional Monarchy', 'Nassau'), ('BHR', 'Bahrain', 'Asia', 'Middle East', '1971', 'Al-Bahrayn', 'Monarchy (Emirate)', 'Manama'), ('BGD', 'Bangladesh', 'Asia', 'Southern and Central Asia', '1971', 'Bangladesh', 'Republic', 'Dhaka'), ('BRB', 'Barbados', 'North America', 'Caribbean', '1966', 'Barbados', 'Constitutional Monarchy', 'Bridgetown'), ('BEL', 'Belgium', 'Europe', 'Western Europe', '1830', 'Belgie/Belgique', 'Constitutional Monarchy, Federation', 'Brussels'), ('BLZ', 'Belize', 'North America', 'Central America', '1981', 'Belize', 'Constitutional Monarchy', 'Belmopan'), ('BEN', 'Benin', 'Africa', 'Western Africa', '1960', 'Benin', 'Republic', 'Porto-Novo'), ('BMU', 'Bermuda', 'North America', 'North America', None, 'Bermuda', 'Dependent Territory of the UK', 'Hamilton'), ('BTN', 'Bhutan', 'Asia', 'Southern and Central Asia', '1910', 'Druk-Yul', 'Monarchy', 'Thimphu'), ('BOL', 'Bolivia', 'South America', 'South America', '1825', 'Bolivia', 'Republic', 'La Paz'), ('BIH', 'Bosnia and Herzegovina', 'Europe', 'Southern Europe', '1992', 'Bosna i Hercegovina', 'Federal Republic', 'Sarajevo'), ('BWA', 'Botswana', 'Africa', 'Southern Africa', '1966', 'Botswana', 'Republic', 'Gaborone'), ('BRA', 'Brazil', 'South America', 'South America', '1822', 'Brasil', 'Federal Republic', 'Brasilia'), ('GBR', 'United Kingdom', 'Europe', 'British Islands', '1066', 'United Kingdom', 'Constitutional Monarchy', 'London'), ('VGB', 'Virgin Islands, British', 'North America', 'Caribbean', None, 'British Virgin Islands', 'Dependent Territory of the UK', 'Road Town'), ('BRN', 'Brunei', 'Asia', 'Southeast Asia', '1984', 'Brunei Darussalam', 'Monarchy (Sultanate)', 'Bandar Seri Begawan'), ('BGR', 'Bulgaria', 'Europe', 'Eastern Europe', '1908', 'Balgarija', 'Republic', 'Sofia'), ('BFA', 'Burkina Faso', 'Africa', 'Western Africa', '1960', 'Burkina Faso', 'Republic', 'Ouagadougou'), ('BDI', 'Burundi', 'Africa', 'Eastern Africa', '1962', 'Burundi/Uburundi', 'Republic', 'Bujumbura'), ('CYM', 'Cayman Islands', 'North America', 'Caribbean', None, 'Cayman Islands', 'Dependent Territory of the UK', 'George Town'), ('CHL', 'Chile', 'South America', 'South America', '1810', 'Chile', 'Republic', 'Santiago'), ('CRI', 'Costa Rica', 'North America', 'Central America', '1821', 'Costa Rica', 'Republic', 'San Jose'), ('DJI', 'Djibouti', 'Africa', 'Eastern Africa', '1977', 'Djibouti/Jibuti', 'Republic', 'Djibouti'), ('DMA', 'Dominica', 'North America', 'Caribbean', '1978', 'Dominica', 'Republic', 'Roseau'), ('DOM', 'Dominican Republic', 'North America', 'Caribbean', '1844', 'Republica Dominicana', 'Republic', 'Santo Domingo'), ('ECU', 'Ecuador', 'South America', 'South America', '1822', 'Ecuador', 'Republic', 'Quito'), ('EGY', 'Egypt', 'Africa', 'Northern Africa', '1922', 'Misr', 'Republic', 'Cairo'), ('SLV', 'El Salvador', 'North America', 'Central America', '1841', 'El Salvador', 'Republic', 'San Salvador'), ('ERI', 'Eritrea', 'Africa', 'Eastern Africa', '1993', 'Ertra', 'Republic', 'Asmara'), ('ESP', 'Spain', 'Europe', 'Southern Europe', '1492', 'Espana', 'Constitutional Monarchy', 'Madrid'), ('ZAF', 'South Africa', 'Africa', 'Southern Africa', '1910', 'South Africa', 'Republic', 'Pretoria'), ('ETH', 'Ethiopia', 'Africa', 'Eastern Africa', '-1000', 'YeItyop´iya', 'Republic', 'Addis Ababa'), ('FJI', 'Fiji Islands', 'Oceania', 'Melanesia', '1970', 'Fiji Islands', 'Republic', 'Suva'), ('PHL', 'Philippines', 'Asia', 'Southeast Asia', '1946', 'Pilipinas', 'Republic', 'Manila'), ('FRO', 'Faroe Islands', 'Europe', 'Nordic Countries', None, 'Foroyar', 'Part of Denmark', 'Torshavn'), ('GAB', 'Gabon', 'Africa', 'Central Africa', '1960', 'Le Gabon', 'Republic', 'Libreville'), ('GMB', 'Gambia', 'Africa', 'Western Africa', '1965', 'The Gambia', 'Republic', 'Banjul'), ('GEO', 'Georgia', 'Asia', 'Middle East', '1991', 'Sakartvelo', 'Republic', 'Tbilisi'), ('GHA', 'Ghana', 'Africa', 'Western Africa', '1957', 'Ghana', 'Republic', 'Accra'), ('GIB', 'Gibraltar', 'Europe', 'Southern Europe', None, 'Gibraltar', 'Dependent Territory of the UK', None), ('GRD', 'Grenada', 'North America', 'Caribbean', '1974', 'Grenada', 'Constitutional Monarchy', "Saint George's"), ('GRL', 'Greenland', 'North America', 'North America', None, 'Kalaallit Nunaat/Gronland', 'Part of Denmark', 'Nuuk'), ('GUM', 'Guam', 'Oceania', 'Micronesia', None, 'Guam', 'US Territory', 'Agana'), ('GTM', 'Guatemala', 'North America', 'Central America', '1821', 'Guatemala', 'Republic', 'Guatemala City'), ('GIN', 'Guinea', 'Africa', 'Western Africa', '1958', 'Guinee', 'Republic', 'Conakry'), ('GNB', 'Guinea-Bissau', 'Africa', 'Western Africa', '1974', 'Guine-Bissau', 'Republic', 'Bissau'), ('GUY', 'Guyana', 'South America', 'South America', '1966', 'Guyana', 'Republic', 'Georgetown'), ('HTI', 'Haiti', 'North America', 'Caribbean', '1804', 'Haiti/Dayti', 'Republic', 'Port-au-Prince'), ('HND', 'Honduras', 'North America', 'Central America', '1838', 'Honduras', 'Republic', 'Tegucigalpa'), ('HKG', 'Hong Kong', 'Asia', 'Eastern Asia', None, 'Xianggang/Hong Kong', 'Special Administrative Region of China', None), ('IDN', 'Indonesia', 'Asia', 'Southeast Asia', '1945', 'Indonesia', 'Republic', 'Jakarta'), ('IND', 'India', 'Asia', 'Southern and Central Asia', '1947', 'Bharat/India', 'Federal Republic', 'New Delhi'), ('IRQ', 'Iraq', 'Asia', 'Middle East', '1932', 'Al-´Iraq', 'Republic', 'Baghdad'), ('IRN', 'Iran', 'Asia', 'Southern and Central Asia', '1906', 'Iran', 'Islamic Republic', 'Tehran'), ('IRL', 'Ireland', 'Europe', 'British Islands', '1921', 'Ireland/Eire', 'Republic', 'Dublin'), ('ISL', 'Iceland', 'Europe', 'Nordic Countries', '1944', 'Island', 'Republic', 'Reykjavik'), ('ISR', 'Israel', 'Asia', 'Middle East', '1948', 'Yisra\x92el/Isra\x92il', 'Republic', None), ('ITA', 'Italy', 'Europe', 'Southern Europe', '1861', 'Italia', 'Republic', 'Rome'), ('AUT', 'Austria', 'Europe', 'Western Europe', '1918', 'Osterreich', 'Federal Republic', 'Vienna'), ('JAM', 'Jamaica', 'North America', 'Caribbean', '1962', 'Jamaica', 'Constitutional Monarchy', 'Kingston'), ('JPN', 'Japan', 'Asia', 'Eastern Asia', '-660', 'Nihon/Nippon', 'Constitutional Monarchy', 'Tokyo'), ('YEM', 'Yemen', 'Asia', 'Middle East', '1918', 'Al-Yaman', 'Republic', "Sana'a"), ('JOR', 'Jordan', 'Asia', 'Middle East', '1946', 'Al-Urdunn', 'Constitutional Monarchy', 'Amman'), ('KHM', 'Cambodia', 'Asia', 'Southeast Asia', '1953', 'Kampuchea', 'Constitutional Monarchy', 'Phnom Penh'), ('CMR', 'Cameroon', 'Africa', 'Central Africa', '1960', 'Cameroun/Cameroon', 'Republic', 'Yaounde'), ('CAN', 'Canada', 'North America', 'North America', '1867', 'Canada', 'Constitutional Monarchy, Federation', 'Ottawa'), ('CPV', 'Cape Verde', 'Africa', 'Western Africa', '1975', 'Cabo Verde', 'Republic', 'Praia'), ('KAZ', 'Kazakhstan', 'Asia', 'Southern and Central Asia', '1991', 'Qazaqstan', 'Republic', 'Astana'), ('KEN', 'Kenya', 'Africa', 'Eastern Africa', '1963', 'Kenya', 'Republic', 'Nairobi'), ('CAF', 'Central African Republic', 'Africa', 'Central Africa', '1960', 'Centrafrique/Be-Afrika', 'Republic', 'Bangui'), ('CHN', 'China', 'Asia', 'Eastern Asia', '-1523', 'Zhongquo', "People'sRepublic", 'Beijing'), ('KGZ', 'Kyrgyzstan', 'Asia', 'Southern and Central Asia', '1991', 'Kyrgyzstan', 'Republic', 'Bishkek'), ('KIR', 'Kiribati', 'Oceania', 'Micronesia', '1979', 'Kiribati', 'Republic', 'Tarawa'), ('COL', 'Colombia', 'South America', 'South America', '1810', 'Colombia', 'Republic', 'Bogota'), ('COM', 'Comoros', 'Africa', 'Eastern Africa', '1975', 'Komori/Comores', 'Republic', 'Moroni'), ('COG', 'Congo', 'Africa', 'Central Africa', '1960', 'Congo', 'Republic', 'Brazzaville'), ('COD', 'Congo, The Democratic Republic of the', 'Africa', 'Central Africa', '1960', 'Republique Democratique du Congo', 'Republic', 'Kinshasa'), ('PRK', 'North Korea', 'Asia', 'Eastern Asia', '1948', 'Choson Minjujuui In´min Konghwaguk (Bukhan)', 'Socialistic Republic', 'Pyongyang'), ('KOR', 'South Korea', 'Asia', 'Eastern Asia', '1948', 'Taehan Min\x92guk (Namhan)', 'Republic', 'Seoul'), ('GRC', 'Greece', 'Europe', 'Southern Europe', '1830', 'Ellada', 'Republic', 'Athens'), ('HRV', 'Croatia', 'Europe', 'Southern Europe', '1991', 'Hrvatska', 'Republic', 'Zagreb'), ('CUB', 'Cuba', 'North America', 'Caribbean', '1902', 'Cuba', 'Socialistic Republic', 'Havana'), ('KWT', 'Kuwait', 'Asia', 'Middle East', '1961', 'Al-Kuwayt', 'Constitutional Monarchy (Emirate)', 'Kuwait City'), ('CYP', 'Cyprus', 'Asia', 'Middle East', '1960', 'Kypros/Kibris', 'Republic', 'Nicosia'), ('LAO', 'Laos', 'Asia', 'Southeast Asia', '1953', 'Lao', 'Republic', 'Vientiane'), ('LVA', 'Latvia', 'Europe', 'Baltic Countries', '1991', 'Latvija', 'Republic', 'Riga'), ('LSO', 'Lesotho', 'Africa', 'Southern Africa', '1966', 'Lesotho', 'Constitutional Monarchy', 'Maseru'), ('LBN', 'Lebanon', 'Asia', 'Middle East', '1941', 'Lubnan', 'Republic', 'Beirut'), ('LBR', 'Liberia', 'Africa', 'Western Africa', '1847', 'Liberia', 'Republic', 'Monrovia'), ('LBY', 'Libya', 'Africa', 'Northern Africa', '1951', 'Libiya', 'Socialistic State', 'Tripoli'), ('LIE', 'Liechtenstein', 'Europe', 'Western Europe', '1806', 'Liechtenstein', 'Constitutional Monarchy', 'Vaduz'), ('LTU', 'Lithuania', 'Europe', 'Baltic Countries', '1991', 'Lietuva', 'Republic', 'Vilnius'), ('LUX', 'Luxembourg', 'Europe', 'Western Europe', '1867', 'Luxembourg/Letzebuerg', 'Constitutional Monarchy', 'Luxembourg'), ('MAC', 'Macao', 'Asia', 'Eastern Asia', None, 'Macau/Aomen', 'Special Administrative Region of China', None), ('MDG', 'Madagascar', 'Africa', 'Eastern Africa', '1960', 'Madagasikara/Madagascar', 'Federal Republic', 'Antananarivo'), ('MKD', 'Macedonia', 'Europe', 'Southern Europe', '1991', 'Makedonija', 'Republic', 'Skopje'), ('MWI', 'Malawi', 'Africa', 'Eastern Africa', '1964', 'Malawi', 'Republic', 'Lilongwe'), ('MDV', 'Maldives', 'Asia', 'Southern and Central Asia', '1965', 'Dhivehi Raajje/Maldives', 'Republic', 'Male'), ('MYS', 'Malaysia', 'Asia', 'Southeast Asia', '1957', 'Malaysia', 'Constitutional Monarchy, Federation', 'Kuala Lumpur'), ('MLI', 'Mali', 'Africa', 'Western Africa', '1960', 'Mali', 'Republic', 'Bamako'), ('MLT', 'Malta', 'Europe', 'Southern Europe', '1964', 'Malta', 'Republic', 'Valletta'), ('MAR', 'Morocco', 'Africa', 'Northern Africa', '1956', 'Al-Maghrib', 'Constitutional Monarchy', 'Rabat'), ('MHL', 'Marshall Islands', 'Oceania', 'Micronesia', '1990', 'Marshall Islands/Majol', 'Republic', 'Majuro'), ('MRT', 'Mauritania', 'Africa', 'Western Africa', '1960', 'Muritaniya/Mauritanie', 'Republic', 'Nouakchott'), ('MUS', 'Mauritius', 'Africa', 'Eastern Africa', '1968', 'Mauritius', 'Republic', 'Port Louis'), ('MEX', 'Mexico', 'North America', 'Central America', '1810', 'Mexico', 'Federal Republic', 'Mexico City'), ('FSM', 'Micronesia, Federated States of', 'Oceania', 'Micronesia', '1990', 'Micronesia', 'Federal Republic', 'Palikir'), ('MDA', 'Moldova', 'Europe', 'Eastern Europe', '1991', 'Moldova', 'Republic', 'Chisinau'), ('MCO', 'Monaco', 'Europe', 'Western Europe', '1861', 'Monaco', 'Constitutional Monarchy', 'Monaco'), ('MNG', 'Mongolia', 'Asia', 'Eastern Asia', '1921', 'Mongol Uls', 'Republic', 'Ulaanbaatar'), ('MOZ', 'Mozambique', 'Africa', 'Eastern Africa', '1975', 'Mocambique', 'Republic', 'Maputo'), ('MMR', 'Myanmar', 'Asia', 'Southeast Asia', '1948', 'Myanma Pye', 'Republic', 'Naypyidaw'), ('NAM', 'Namibia', 'Africa', 'Southern Africa', '1990', 'Namibia', 'Republic', 'Windhoek'), ('NRU', 'Nauru', 'Oceania', 'Micronesia', '1968', 'Naoero/Nauru', 'Republic', 'Yaren District'), ('NPL', 'Nepal', 'Asia', 'Southern and Central Asia', '1769', 'Nepal', 'Constitutional Monarchy', 'Kathmandu'), ('NIC', 'Nicaragua', 'North America', 'Central America', '1838', 'Nicaragua', 'Republic', 'Managua'), ('NER', 'Niger', 'Africa', 'Western Africa', '1960', 'Niger', 'Republic', 'Niamey'), ('NGA', 'Nigeria', 'Africa', 'Western Africa', '1960', 'Nigeria', 'Federal Republic', 'Abuja'), ('NOR', 'Norway', 'Europe', 'Nordic Countries', '1905', 'Norge', 'Constitutional Monarchy', 'Oslo'), ('CIV', "Cote d'Ivoire", 'Africa', 'Western Africa', '1960', 'Cote d\x92Ivoire', 'Republic', 'Yamoussoukro'), ('OMN', 'Oman', 'Asia', 'Middle East', '1951', '´Uman', 'Monarchy (Sultanate)', 'Muscat'), ('PAK', 'Pakistan', 'Asia', 'Southern and Central Asia', '1947', 'Pakistan', 'Republic', 'Islamabad'), ('PLW', 'Palau', 'Oceania', 'Micronesia', '1994', 'Belau/Palau', 'Republic', 'Koror'), ('PAN', 'Panama', 'North America', 'Central America', '1903', 'Panama', 'Republic', 'Panama City'), ('PNG', 'Papua New Guinea', 'Oceania', 'Melanesia', '1975', 'Papua New Guinea/Papua Niugini', 'Constitutional Monarchy', 'Port Moresby'), ('PRY', 'Paraguay', 'South America', 'South America', '1811', 'Paraguay', 'Republic', 'Asuncion'), ('PER', 'Peru', 'South America', 'South America', '1821', 'Peru/Piruw', 'Republic', 'Lima'), ('MNP', 'Northern Mariana Islands', 'Oceania', 'Micronesia', None, 'Northern Mariana Islands', 'Commonwealth of the US', 'Saipan'), ('PRT', 'Portugal', 'Europe', 'Southern Europe', '1143', 'Portugal', 'Republic', 'Lisbon'), ('PRI', 'Puerto Rico', 'North America', 'Caribbean', None, 'Puerto Rico', 'Commonwealth of the US', 'San Juan'), ('POL', 'Poland', 'Europe', 'Eastern Europe', '1918', 'Polska', 'Republic', 'Warsaw'), ('GNQ', 'Equatorial Guinea', 'Africa', 'Central Africa', '1968', 'Guinea Ecuatorial', 'Republic', 'Malabo'), ('QAT', 'Qatar', 'Asia', 'Middle East', '1971', 'Qatar', 'Monarchy', 'Doha'), ('FRA', 'France', 'Europe', 'Western Europe', '843', 'France', 'Republic', 'Paris'), ('PYF', 'French Polynesia', 'Oceania', 'Polynesia', None, 'Polynesie francaise', 'Nonmetropolitan Territory of France', 'Papeete'), ('RWA', 'Rwanda', 'Africa', 'Eastern Africa', '1962', 'Rwanda/Urwanda', 'Republic', 'Kigali'), ('SWE', 'Sweden', 'Europe', 'Nordic Countries', '836', 'Sverige', 'Constitutional Monarchy', 'Stockholm'), ('KNA', 'Saint Kitts and Nevis', 'North America', 'Caribbean', '1983', 'Saint Kitts and Nevis', 'Constitutional Monarchy', 'Basseterre'), ('LCA', 'Saint Lucia', 'North America', 'Caribbean', '1979', 'Saint Lucia', 'Constitutional Monarchy', 'Castries'), ('VCT', 'Saint Vincent and the Grenadines', 'North America', 'Caribbean', '1979', 'Saint Vincent and the Grenadines', 'Constitutional Monarchy', 'Kingstown'), ('DEU', 'Germany', 'Europe', 'Western Europe', '1955', 'Deutschland', 'Federal Republic', 'Berlin'), ('SLB', 'Solomon Islands', 'Oceania', 'Melanesia', '1978', 'Solomon Islands', 'Constitutional Monarchy', 'Honiara'), ('ZMB', 'Zambia', 'Africa', 'Eastern Africa', '1964', 'Zambia', 'Republic', 'Lusaka'), ('WSM', 'Samoa', 'Oceania', 'Polynesia', '1962', 'Samoa', 'Parlementary Monarchy', 'Apia'), ('SMR', 'San Marino', 'Europe', 'Southern Europe', '885', 'San Marino', 'Republic', 'San Marino'), ('STP', 'Sao Tome and Principe', 'Africa', 'Central Africa', '1975', 'Sao Tome e Principe', 'Republic', 'Sao Tome'), ('SAU', 'Saudi Arabia', 'Asia', 'Middle East', '1932', 'Al-´Arabiya as-Sa´udiya', 'Monarchy', 'Riyadh'), ('SEN', 'Senegal', 'Africa', 'Western Africa', '1960', 'Senegal/Sounougal', 'Republic', 'Dakar'), ('SYC', 'Seychelles', 'Africa', 'Eastern Africa', '1976', 'Sesel/Seychelles', 'Republic', 'Victoria'), ('SLE', 'Sierra Leone', 'Africa', 'Western Africa', '1961', 'Sierra Leone', 'Republic', 'Freetown'), ('SGP', 'Singapore', 'Asia', 'Southeast Asia', '1965', 'Singapore/Singapura/Xinjiapo/Singapur', 'Republic', 'Singapore'), ('SVK', 'Slovakia', 'Europe', 'Eastern Europe', '1993', 'Slovensko', 'Republic', 'Bratislava'), ('SVN', 'Slovenia', 'Europe', 'Southern Europe', '1991', 'Slovenija', 'Republic', 'Ljubljana'), ('SOM', 'Somalia', 'Africa', 'Eastern Africa', '1960', 'Soomaaliya', 'Republic', 'Mogadishu'), ('LKA', 'Sri Lanka', 'Asia', 'Southern and Central Asia', '1948', 'Sri Lanka/Ilankai', 'Republic', 'Colombo'), ('SDN', 'Sudan', 'Africa', 'Northern Africa', '1956', 'As-Sudan', 'Islamic Republic', 'Khartoum'), ('FIN', 'Finland', 'Europe', 'Nordic Countries', '1917', 'Suomi', 'Republic', 'Helsinki'), ('SUR', 'Suriname', 'South America', 'South America', '1975', 'Suriname', 'Republic', 'Paramaribo'), ('SWZ', 'Swaziland', 'Africa', 'Southern Africa', '1968', 'kaNgwane', 'Monarchy', 'Mbabane'), ('CHE', 'Switzerland', 'Europe', 'Western Europe', '1499', 'Schweiz/Suisse/Svizzera/Svizra', 'Federation', 'Bern'), ('SYR', 'Syria', 'Asia', 'Middle East', '1941', 'Suriya', 'Republic', 'Damascus'), ('TJK', 'Tajikistan', 'Asia', 'Southern and Central Asia', '1991', 'Tocikiston', 'Republic', 'Dushanbe'), ('TZA', 'Tanzania', 'Africa', 'Eastern Africa', '1961', 'Tanzania', 'Republic', 'Dodoma'), ('DNK', 'Denmark', 'Europe', 'Nordic Countries', '800', 'Danmark', 'Constitutional Monarchy', 'Copenhagen'), ('THA', 'Thailand', 'Asia', 'Southeast Asia', '1350', 'Prathet Thai', 'Constitutional Monarchy', 'Bangkok'), ('TGO', 'Togo', 'Africa', 'Western Africa', '1960', 'Togo', 'Republic', 'Lome'), ('TON', 'Tonga', 'Oceania', 'Polynesia', '1970', 'Tonga', 'Monarchy', "Nuku'alofa"), ('TTO', 'Trinidad and Tobago', 'North America', 'Caribbean', '1962', 'Trinidad and Tobago', 'Republic', 'Port-of-Spain'), ('TCD', 'Chad', 'Africa', 'Central Africa', '1960', 'Tchad/Tshad', 'Republic', "N'Djamena"), ('CZE', 'Czech Republic', 'Europe', 'Eastern Europe', '1993', '¸esko', 'Republic', 'Prague'), ('TUN', 'Tunisia', 'Africa', 'Northern Africa', '1956', 'Tunis/Tunisie', 'Republic', 'Tunis'), ('TUR', 'Turkey', 'Asia', 'Middle East', '1923', 'Turkiye', 'Republic', 'Ankara'), ('TKM', 'Turkmenistan', 'Asia', 'Southern and Central Asia', '1991', 'Turkmenostan', 'Republic', 'Ashgabat'), ('TCA', 'Turks and Caicos Islands', 'North America', 'Caribbean', None, 'The Turks and Caicos Islands', 'Dependent Territory of the UK', 'Grand Turk'), ('TUV', 'Tuvalu', 'Oceania', 'Polynesia', '1978', 'Tuvalu', 'Constitutional Monarchy', 'Funafuti'), ('UGA', 'Uganda', 'Africa', 'Eastern Africa', '1962', 'Uganda', 'Republic', 'Kampala'), ('UKR', 'Ukraine', 'Europe', 'Eastern Europe', '1991', 'Ukrajina', 'Republic', 'Kiev'), ('HUN', 'Hungary', 'Europe', 'Eastern Europe', '1918', 'Magyarorszag', 'Republic', 'Budapest'), ('URY', 'Uruguay', 'South America', 'South America', '1828', 'Uruguay', 'Republic', 'Montevideo'), ('NCL', 'New Caledonia', 'Oceania', 'Melanesia', None, 'Nouvelle-Caledonie', 'Nonmetropolitan Territory of France', "Noum'ea"), ('NZL', 'New Zealand', 'Oceania', 'Australia and New Zealand', '1907', 'New Zealand/Aotearoa', 'Constitutional Monarchy', 'Wellington'), ('UZB', 'Uzbekistan', 'Asia', 'Southern and Central Asia', '1991', 'Uzbekiston', 'Republic', 'Tashkent'), ('BLR', 'Belarus', 'Europe', 'Eastern Europe', '1991', 'Belarus', 'Republic', 'Minsk'), ('VUT', 'Vanuatu', 'Oceania', 'Melanesia', '1980', 'Vanuatu', 'Republic', 'Port-Vila'), ('VEN', 'Venezuela', 'South America', 'South America', '1811', 'Venezuela', 'Federal Republic', 'Caracas'), ('RUS', 'Russian Federation', 'Europe', 'Eastern Europe', '1991', 'Rossija', 'Federal Republic', 'Moscow'), ('VNM', 'Vietnam', 'Asia', 'Southeast Asia', '1945', 'Viet Nam', 'Socialistic Republic', 'Hanoi'), ('EST', 'Estonia', 'Europe', 'Baltic Countries', '1991', 'Eesti', 'Republic', 'Tallinn'), ('USA', 'United States', 'North America', 'North America', '1776', 'United States', 'Federal Republic', 'Washington D.C.'), ('VIR', 'Virgin Islands, U.S.', 'North America', 'Caribbean', None, 'Virgin Islands of the United States', 'US Territory', 'Charlotte Amalie'), ('ZWE', 'Zimbabwe', 'Africa', 'Eastern Africa', '1980', 'Zimbabwe', 'Republic', 'Harare'), ('PSE', 'Palestine', 'Asia', 'Middle East', None, 'Filastin', 'Autonomous Area', None)]
技术支持

您可以通过遍历每一行，让 fetchall() 的输出更清晰些：

for row in cur.execute('SELECT * FROM countries'):
    print(row)
技术支持

('AFG', 'Afghanistan', 'Asia', 'Southern and Central Asia', '1919', 'Afganistan/Afqanestan', 'Islamic Emirate', 'Kabul')
('NLD', 'Netherlands', 'Europe', 'Western Europe', '1581', 'Nederland', 'Constitutional Monarchy', 'Amsterdam')
('ALB', 'Albania', 'Europe', 'Southern Europe', '1912', 'Shqiperia', 'Republic', 'Tirane')
...
('USA', 'United States', 'North America', 'North America', '1776', 'United States', 'Federal Republic', 'Washington D.C.')
('VIR', 'Virgin Islands, U.S.', 'North America', 'Caribbean', None, 'Virgin Islands of the United States', 'US Territory', 'Charlotte Amalie')
('ZWE', 'Zimbabwe', 'Africa', 'Eastern Africa', '1980', 'Zimbabwe', 'Republic', 'Harare')
('PSE', 'Palestine', 'Asia', 'Middle East', None, 'Filastin', 'Autonomous Area', None)
技术支持

现在我们来看看如何在查询中添加 where 子句并执行。让我们获取 code = 'AFG' 的国家详情。

code = ('AFG',)
cur.execute('SELECT * FROM countries WHERE code = ?', code)
print(cur.fetchone())
技术支持

('AFG', 'Afghanistan', 'Asia', 'Southern and Central Asia', '1919', 'Afganistan/Afqanestan', 'Islamic Emirate', 'Kabul')
技术支持

您也可以用下面这种方式获取记录，但上面的写法更安全。

code = 'AFG'
cur.execute("SELECT * FROM countries WHERE code = '%s'" % code)
print(cur.fetchone())
技术支持

('AFG', 'Afghanistan', 'Asia', 'Southern and Central Asia', '1919', 'Afganistan/Afqanestan', 'Islamic Emirate', 'Kabul')
技术支持

您可以逐条插入记录，也可以一次性插入多条。这里我们使用 consumers 表。当前它还没有任何记录。我们从这里开始填充数据。

# One by one
cur.execute("INSERT INTO consumers VALUES (1,'John Doe','[email protected]','A')")
for row in cur.execute('SELECT * FROM consumers'):
    print(row)
技术支持

(1, 'John Doe', '[email protected]', 'A')
技术支持

# Prepare a list of records to be inserted
purchases = [(2,'John Paul','[email protected]','B'),
             (3,'Chris Paul','[email protected]','A'),
            ]

# Use executemany() to insert multiple records at a time
cur.executemany('INSERT INTO consumers VALUES (?,?,?,?)', purchases)
for row in cur.execute('SELECT * FROM consumers'):
    print(row)
技术支持

(1, 'John Doe', '[email protected]', 'A')
(2, 'John Paul', '[email protected]', 'B')
(3, 'Chris Paul', '[email protected]', 'A')
技术支持

您可以在 DB Browser 工具中交叉验证，这些记录也应会显示。注意，除非您提交这些事务，否则不会生效。只需调用先前创建的 Connection 对象的 commit() 方法即可提交/保存。

conn.commit()
技术支持

现在您应该能看到这些数据行了：

一旦完成操作，关闭数据库连接是良好的编程实践。但在此之前，需要将更改永久保存，正如上面通过 commit() 方法所示。

# Closing the DB connection
conn.close()
技术支持

恭喜！

感谢您读完本教程。本文向您介绍了功能强大且轻量的 RDBMS——SQLite，并演示了如何使用 Python 与 SQLite 交互。如有问题，欢迎在评论区留言。

如果您想进一步学习在 Python 中与数据库交互，欢迎学习 DataCamp 的 Introduction to Databases in Python 课程。也可查看我们的 Beginners Guide to SQLite 教程。

主题

SQL Python 数据分析

主题

技术