Don't worry about being ignorant, just staying ignorant. As long as ignorance is merely a temporary state and not a central component of identity, you're fine in the computing world. Don't be afraid to ask
what a spline is when you don't understand some jargon. What follows is a lot to go through; understand what you can now, and re-read it later for the rest.
A bit of advice: selecting all columns (
SELECT *) is generally a bad idea because it introduces a dependence on the order of columns and pulls unnecessary information from the database, which uses resources you don't need to use. The result is brittle code and requires more work, should you change the order of the columns or insert additional columns into the table. Also, the code that handles the response depends on specific columns; selecting all columns makes this dependency implicit rather than explicit. Better to explicitly name the columns you need in the
SELECT statement.
To separate the table names used in the database (which are internal to your application, and shouldn't affect anything the user sees) from the values used in the form, here's a simple example using a dictonary:
Code:
subjects = {'sci': 'Science', 'mth': 'Math', 'lit': 'Literature', 'art': 'Art', 'dflt': 'Science'}
# Preconditions:
# db.name == 'atg_admin'
# columns holds column names as a string, e.g. columns='`year`, `teacher`, `topic`'.
# It doesn't come from user input.
# If choice isn't a valid subject, use the default subject.
# Could also abort action & print an error
if choice not in subjects:
choice='dflt'
statement = "SELECT %s FROM `%s`.`%s`" % (columns, db.name, subjects[choice])
The code checks whether
choice is a valid key. If not, it changes the choice to a valid key. You could instead use a
default dictionary:
Code:
import collections
subjects = collections.defaultdict(lambda: 'Science', {'sci': 'Science', 'mth': 'Math', 'lit': 'Literature', 'art': 'Art'})
...
statement = "SELECT %s FROM `%s`.`%s`" % (columns, db.name, subjects[choice])
Here, you don't need to check whether a key exists. If it doesn't, you'll get the default value rather than a thrown
KeyError.
As the comment in the first sample suggests, you could instead signal an error:
Code:
# class InputError must be defined, as it's not a built-in
if choice not in subjects:
throw InputError("'%s' is not a valid choice." % (choice))
statement = "SELECT %s FROM `%s`.`%s`" % (columns, db.name, subjects[choice])
where you define an
exception handler elsewhere that sets the HTTP status and outputs an error message. Of course, the above code shouldn't appear as-is, but should be integrated into functions or methods.
The link on "
coupling" in my previous post explains one of the concepts involved. "Coupling" is a broad topic and merits a fair bit of time to study it. A newer programming paradigm,
Aspect Oriented Programming, focuses on reducing coupling. Don't be too concerned with AOP right now; it should remain a point on the horizon.
Another concept involved is "information disclosure". I don't have any links on that topic at large, but "
Writing Error Messages for Security Features" has a section on "
information disclosure in error messages", which explains it well enough.
"Atomic" refers to values that can't be broken down, as opposed to "compound" or "aggregate" values, which are collections of other values. This comes from the original Greek meaning of
atom: uncut. Lists, dictionaries and structures are examples of compound types; "[0,2,4]" and "{'one': 1, 'zed': 0}" are compound values. When it comes to databases, you might see the terms "simple domains", "nonsimple domains" and "complex domains"; the former basically means "atomic types" and the latter two mean "aggregate types".
"Interpolation" here means substituting a value into a string. The
% operator in Python is an interpolation operator. PHP and Python interpolate values from variables quite easily: "
SELECT $columns FROM $table" will interpolate the values of the variables
$columns and
$table. In Ruby, you use
#{...}: "
SELECT #{columns} FROM #{table}".
#{...} lets you interpolate expressions, not just variables, which makes it more powerful.
A database driver is the software that supports interaction with the database. For Python, you might use
MySQLdb or
MySQL Connector/Python.
Queries have their own language: their own grammar, vocabulary and parts-of-speech. As natural languages have nouns, verbs, adjectives, noun-phrases &c., SQL has commands (or actions), clauses, conditions, expressions, values, identifiers (names) and so on (if you're curious, you can study
SQL's grammar in Backus-Naur form, or
BNF). Prepared statements let you treat a very limited set of these as parameters, which are like function arguments, that can be replaced at a later point by specific values. (The DB driver will bind values in prepared statements rather like the Python interpreter binding values to variables when a function is called.) This limited set of terms usually doesn't include identifiers (column names, table names) or lists (those compound values previously mentioned). For example, consider the statement:
Code:
SELECT number, name, symbol
FROM elements
WHERE group='p' AND oxidation IN (1,3,5)
which selects elements from a hypothetical periodic table. For most DB drivers, none of the identifiers (number, name, symbol, elements, group and valence) can be turned into parameters, nor the action (SELECT), nor the clause predicates (FROM, WHERE), nor the compound value ((1,3,5)) used in the IN statement, nor any phrases (e.g. group='p', FROM elements). The only part that can be parameterized is the value 'p'.
"Structure" refers to data structure, i.e. how you've defined your tables: the column names, their types, their primary and foreign keys. I've also seen the term "intension" refer to this, as opposed to "extension"; the intension of a table is
meta-information (information about the table), while the extension of a table is the information stored in a table. Basically, when using SQL the structure is your
CREATE statement. Add the
ALTER and
DROP commands and you've got the
Data Definition Language (DDL) portion of SQL, which deals exclusively with intensions. Extensions are handled by the
Data Manipulation Language (DML) portion:
SELECT,
INSERT,
UPDATE and
DELETE statements.
As for the
gruff nature of my writing, it doesn't communicate annoyance. It's just a very straight-forward, no-nonsense communication style.
