Chapter 15: Python and Databases Flashcards
One of the values within a tuple. More commonly called a “column” or “field”.
attribute
When we tell the database to enforce a rule on a field or a row in a table. A common one is to insist that there can be no duplicate values in a particular field (i.e., all the values must be unique).
constraint
Allows you to execute SQL commands in a database and retrieve data from the database. Similar to a socket or file handle for network connections and files, respectively.
cursor
A piece of software that allows you to directly connect to a database and manipulate the database directly without writing a program.
database browser
A numeric key that points to the primary key of a row in another table. Establish relationships between rows stored in different tables.
foreign key
eg, artist_id (foreign key) in Track table links to id (primary key) in Artist table
Additional data that the database software maintains as rows and inserts into a table to make lookups very fast.
index
A key that the “outside world” uses to look up a particular row
eg. in a table of user accounts, a person’s email address might be a good candidate as one for the user’s data
logical key
Designing a data model so that no data is replicated. We store each item of data at one place in the database and reference it elsewhere using a foreign key.
normalization
A numeric key assigned to each row that is used to refer to rows in another table. Often the database is configured to automatically assign them as rows are inserted.
primary key
eg. id (primary key) in Artist table links to artist_id (foreign key) in Track table
An area within a database that contains tuples and attributes. More typically called a “table”.
relation
A single entry in a database table that is a set of attributes. More typically called “row”.
tuple
SQL command that deletes the table and all of its contents from the database.
Can’t be undone
DROP TABLE
DROP TABLE IF EXISTS
method to perform commands on database once cursor is implemented
.execute()
cur.execute(‘SQL KEYWORD table’)
SQL command to make a new table
cur.execute(‘CREATE TABLE table (column TYPE)’)
SQL command to query info from a database
SELECT
SQL keyword to rename column or table
AS
return unique values
DISTINCT
filter results based on specified condition
WHERE
use with WHERE
match closely matching names using wildcard characters
LIKE
use with WHERE
return results within a range.
Numbers: includes 2nd condition
Text: includes first specified character in 2nd condition
BETWEEN
use with WHERE
meets all conditions
AND
use with WHERE
meets any condition
OR
sorts results
ASC: default
DESC: high to low, Z-A
ORDER BY
specifies number of rows to return
always at end of query
LIMIT
if-then conditional
CASE
WHEN … THEN …
ELSE …
END
CASE
creates a new table
CREATE TABLE
adds a new row to a table
INSERT INTO
INSERT INTO table (columns)
edits table column(s)
ALTER TABLE
edits table row(s)
UPDATE
removes rows from a table
needs WHERE clause to specify which rows
DELETE FROM
specifies how column can be used
eg. data types
constraints
counts the number of rows
COUNT()
sum of the values in a column
SUM()
the largest / smallest value
MAX() /
MIN()
the average of the values in a column
AVG()
round the values in a column
ROUND()
used with SELECT to arrange identical data into groups
WHERE
answer
ORDER BY
LIMIT
GROUP BY
use to filter groups
GROUP BY
answer
ORDER BY
LIMIT
HAVING
SQL keyword specifying row data to be inserted into table
VALUES
‘INSERT INTO table (column1, column2) VALUES (?, ?)’ , (values tuple)
SQL method that forces data to be written to the database file after editing
commit()
eg. conn.commit()
SQL keyword used with UPDATE command to change data in table
Similar to find and replace.
SET
UPDATE table SET column = value
CRUD for INSERT INTO, SELECT, UPDATE, and DELETE FROM commands
Create
Read
Update
Delete
SQL clause to extract data from multiple tables and combine.
Used with ON condition to define how data is to be connected
JOIN
SELECT title, plays, name, eyes
FROM Track JOIN Artist
ON Track.artist_id = Artist.id
Joins artist data and track data
SQL command to automatically create primary keys
PRIMARY KEY (no value)
CREATE TABLE table (id INTEGER PRIMARY KEY, other columns)
SQL method to check last automatically inserted primary key
SELECT last_insert_rowid()
SQL command to mark a logical key (text column) that tells the program to store extra information to look up that column faster
CREATE INDEX index_name ON table (column)
SQL constraint keyword to ensure no duplicates
UNIQUE
CREATE UNIQUE INDEX
SQL command to insert a new record if the name is not already there
INSERT OR IGNORE INTO table (columns)
An intermediate table that functions as the “many” end in a many-to-one relationship between two other tables
junction / through / connector / join table
CREATE TABLE Member (
user_id INTEGER,
course_id INTEGER,
PRIMARY KEY (user_id, course_id))
