From 92d1064727d6b7f4136f9c09ab52ae15e3676afe Mon Sep 17 00:00:00 2001 From: Erlend Egeberg Aasland Date: Wed, 19 May 2021 09:41:19 +0200 Subject: bpo-44106: Improve sqlite3 example database contents (GH-26027) --- Doc/includes/sqlite3/createdb.py | 10 +++++----- Doc/includes/sqlite3/ctx_manager.py | 8 ++++---- Doc/includes/sqlite3/execsql_fetchonerow.py | 10 +++++----- Doc/includes/sqlite3/execsql_printall_1.py | 2 +- Doc/includes/sqlite3/execute_1.py | 13 ++++++------- Doc/includes/sqlite3/insert_more_langs.py | 18 ++++++++++++++++++ Doc/includes/sqlite3/insert_more_people.py | 18 ------------------ Doc/includes/sqlite3/shortcut_methods.py | 16 ++++++++-------- Doc/includes/sqlite3/simple_tableprinter.py | 5 +---- Doc/tools/susp-ignored.csv | 3 +-- 10 files changed, 49 insertions(+), 54 deletions(-) create mode 100644 Doc/includes/sqlite3/insert_more_langs.py delete mode 100644 Doc/includes/sqlite3/insert_more_people.py diff --git a/Doc/includes/sqlite3/createdb.py b/Doc/includes/sqlite3/createdb.py index ee2950b..4970212 100644 --- a/Doc/includes/sqlite3/createdb.py +++ b/Doc/includes/sqlite3/createdb.py @@ -12,15 +12,15 @@ if os.path.exists(DB_FILE): con = sqlite3.connect(DB_FILE) cur = con.cursor() cur.execute(""" - create table people + create table lang ( - name_last varchar(20), - age integer + name varchar(20), + first_appeared integer ) """) -cur.execute("insert into people (name_last, age) values ('Yeltsin', 72)") -cur.execute("insert into people (name_last, age) values ('Putin', 51)") +cur.execute("insert into lang (name, first_appeared) values ('Forth', 1970)") +cur.execute("insert into lang (name, first_appeared) values ('Ada', 1980)") con.commit() diff --git a/Doc/includes/sqlite3/ctx_manager.py b/Doc/includes/sqlite3/ctx_manager.py index 6db77d4..2e1175e 100644 --- a/Doc/includes/sqlite3/ctx_manager.py +++ b/Doc/includes/sqlite3/ctx_manager.py @@ -1,19 +1,19 @@ import sqlite3 con = sqlite3.connect(":memory:") -con.execute("create table person (id integer primary key, firstname varchar unique)") +con.execute("create table lang (id integer primary key, name varchar unique)") # Successful, con.commit() is called automatically afterwards with con: - con.execute("insert into person(firstname) values (?)", ("Joe",)) + con.execute("insert into lang(name) values (?)", ("Python",)) # con.rollback() is called after the with block finishes with an exception, the # exception is still raised and must be caught try: with con: - con.execute("insert into person(firstname) values (?)", ("Joe",)) + con.execute("insert into lang(name) values (?)", ("Python",)) except sqlite3.IntegrityError: - print("couldn't add Joe twice") + print("couldn't add Python twice") # Connection object used as context manager only commits or rollbacks transactions, # so the connection object should be closed manually diff --git a/Doc/includes/sqlite3/execsql_fetchonerow.py b/Doc/includes/sqlite3/execsql_fetchonerow.py index 115bcb5..0ca7e14 100644 --- a/Doc/includes/sqlite3/execsql_fetchonerow.py +++ b/Doc/includes/sqlite3/execsql_fetchonerow.py @@ -3,17 +3,17 @@ import sqlite3 con = sqlite3.connect("mydb") cur = con.cursor() -SELECT = "select name_last, age from people order by age, name_last" +SELECT = "select name, first_appeared from people order by first_appeared, name" # 1. Iterate over the rows available from the cursor, unpacking the -# resulting sequences to yield their elements (name_last, age): +# resulting sequences to yield their elements (name, first_appeared): cur.execute(SELECT) -for (name_last, age) in cur: - print('%s is %d years old.' % (name_last, age)) +for name, first_appeared in cur: + print(f"The {name} programming language appeared in {first_appeared}.") # 2. Equivalently: cur.execute(SELECT) for row in cur: - print('%s is %d years old.' % (row[0], row[1])) + print(f"The {row[0]} programming language appeared in {row[1]}.") con.close() diff --git a/Doc/includes/sqlite3/execsql_printall_1.py b/Doc/includes/sqlite3/execsql_printall_1.py index 19306e6..b3b42b5 100644 --- a/Doc/includes/sqlite3/execsql_printall_1.py +++ b/Doc/includes/sqlite3/execsql_printall_1.py @@ -7,7 +7,7 @@ con = sqlite3.connect("mydb") cur = con.cursor() # Execute the SELECT statement: -cur.execute("select * from people order by age") +cur.execute("select * from lang order by first_appeared") # Retrieve all rows as a sequence and print that sequence: print(cur.fetchall()) diff --git a/Doc/includes/sqlite3/execute_1.py b/Doc/includes/sqlite3/execute_1.py index 42aad4d..ee0000e 100644 --- a/Doc/includes/sqlite3/execute_1.py +++ b/Doc/includes/sqlite3/execute_1.py @@ -2,22 +2,21 @@ import sqlite3 con = sqlite3.connect(":memory:") cur = con.cursor() -cur.execute("create table lang (lang_name, lang_age)") +cur.execute("create table lang (name, first_appeared)") # This is the qmark style: -cur.execute("insert into lang values (?, ?)", ("C", 49)) +cur.execute("insert into lang values (?, ?)", ("C", 1972)) # The qmark style used with executemany(): lang_list = [ - ("Fortran", 64), - ("Python", 30), - ("Go", 11), + ("Fortran", 1957), + ("Python", 1991), + ("Go", 2009), ] cur.executemany("insert into lang values (?, ?)", lang_list) # And this is the named style: -cur.execute("select * from lang where lang_name=:name and lang_age=:age", - {"name": "C", "age": 49}) +cur.execute("select * from lang where first_appeared=:year", {"year": 1972}) print(cur.fetchall()) con.close() diff --git a/Doc/includes/sqlite3/insert_more_langs.py b/Doc/includes/sqlite3/insert_more_langs.py new file mode 100644 index 0000000..ceef949 --- /dev/null +++ b/Doc/includes/sqlite3/insert_more_langs.py @@ -0,0 +1,18 @@ +import sqlite3 + +con = sqlite3.connect("mydb") + +cur = con.cursor() + +languages = ( + ("Smalltalk", 1972), + ("Swift", 2014), +) + +for lang in languages: + cur.execute("insert into lang (name, first_appeared) values (?, ?)", lang) + +# The changes will not be saved unless the transaction is committed explicitly: +con.commit() + +con.close() diff --git a/Doc/includes/sqlite3/insert_more_people.py b/Doc/includes/sqlite3/insert_more_people.py deleted file mode 100644 index 10cf937..0000000 --- a/Doc/includes/sqlite3/insert_more_people.py +++ /dev/null @@ -1,18 +0,0 @@ -import sqlite3 - -con = sqlite3.connect("mydb") - -cur = con.cursor() - -newPeople = ( - ('Lebed' , 53), - ('Zhirinovsky' , 57), - ) - -for person in newPeople: - cur.execute("insert into people (name_last, age) values (?, ?)", person) - -# The changes will not be saved unless the transaction is committed explicitly: -con.commit() - -con.close() diff --git a/Doc/includes/sqlite3/shortcut_methods.py b/Doc/includes/sqlite3/shortcut_methods.py index 98a3941..48ea6fa 100644 --- a/Doc/includes/sqlite3/shortcut_methods.py +++ b/Doc/includes/sqlite3/shortcut_methods.py @@ -1,23 +1,23 @@ import sqlite3 -persons = [ - ("Hugo", "Boss"), - ("Calvin", "Klein") - ] +langs = [ + ("C++", 1985), + ("Objective-C", 1984), +] con = sqlite3.connect(":memory:") # Create the table -con.execute("create table person(firstname, lastname)") +con.execute("create table lang(name, first_appeared)") # Fill the table -con.executemany("insert into person(firstname, lastname) values (?, ?)", persons) +con.executemany("insert into lang(name, first_appeared) values (?, ?)", langs) # Print the table contents -for row in con.execute("select firstname, lastname from person"): +for row in con.execute("select name, first_appeared from lang"): print(row) -print("I just deleted", con.execute("delete from person").rowcount, "rows") +print("I just deleted", con.execute("delete from lang").rowcount, "rows") # close is not a shortcut method and it's not called automatically, # so the connection object should be closed manually diff --git a/Doc/includes/sqlite3/simple_tableprinter.py b/Doc/includes/sqlite3/simple_tableprinter.py index 148a170..9be6e4f 100644 --- a/Doc/includes/sqlite3/simple_tableprinter.py +++ b/Doc/includes/sqlite3/simple_tableprinter.py @@ -1,13 +1,10 @@ import sqlite3 FIELD_MAX_WIDTH = 20 -TABLE_NAME = 'people' -SELECT = 'select * from %s order by age, name_last' % TABLE_NAME con = sqlite3.connect("mydb") - cur = con.cursor() -cur.execute(SELECT) +cur.execute("select * from lang order by name, first_appeared") # Print a header. for fieldDesc in cur.description: diff --git a/Doc/tools/susp-ignored.csv b/Doc/tools/susp-ignored.csv index d56a2b9..1fde253 100644 --- a/Doc/tools/susp-ignored.csv +++ b/Doc/tools/susp-ignored.csv @@ -209,8 +209,7 @@ library/smtplib,,:port,method must support that as well as a regular host:port library/socket,,::,'5aef:2b::8' library/socket,,:can,"return (can_id, can_dlc, data[:can_dlc])" library/socket,,:len,fds.frombytes(cmsg_data[:len(cmsg_data) - (len(cmsg_data) % fds.itemsize)]) -library/sqlite3,,:name,"cur.execute(""select * from lang where lang_name=:name and lang_age=:age""," -library/sqlite3,,:age,"cur.execute(""select * from lang where lang_name=:name and lang_age=:age""," +library/sqlite3,,:year,"cur.execute(""select * from lang where first_appeared=:year"", {""year"": 1972})" library/sqlite3,,:memory, library/sqlite3,,:path,"db = sqlite3.connect('file:path/to/database?mode=ro', uri=True)" library/ssl,,:My,"Organizational Unit Name (eg, section) []:My Group" -- cgit v0.12