Quick Python Help!

[rewegan]

I'm trying to have something selected from a MySQL table.

I want the user to choose the table
SO i have set the statement as so...

("""SELECT * FROM `%s`""", (choice))

So for example.

If the user has entered "Science"
I have choice = "Science"

So why when I run this, it tells me that
"Table 'atg_admin.'Science'' doesn't exist"

(atg_admin being my database in use).

When it very much does exist.
If I actually type the word "Science" isntead of using %s, it works fine.

Please help.

[misson]

Note the exact quotation marks:

'atg_admin.'Science''

The single quotes around 'Science' indicate a string, rather than a name. The backticks in the query might also be throwing things off.

What is the exact value stored in choice? What method are you calling to prepare the query?

[rewegan]

Ah you're right, it's the string thing. It's being taken from an HTML input and stored as a text string. How do I change that? Instead of the input type being "text" do I change that to something else (on the html page).

And the back ticks are the only thing I could get to work, any form of quotes gives me a SQL syntax error. They have worked with everything else I've done.

Thanks for the reply.

[misson]

It really depends on what you're using to prepare the query.

What method are you calling to prepare the query?

As it says in the sig, questions aren't rhetorical.

Most drivers don't support parameterizing arbitrary portions of queries. Usually you can only parameterize atomic values. No lists, no names, no keywords, which means you'll need to interpolate the value on your own.

Even if the driver supports parameterizing table names, you shouldn't pass this data via forms. To do so is insecure (it leaks information about internal structure) and couples internal structure with external logic. Instead, pass arbitrary values via the form, which you can use as dictionary keys (or resolve in some other way) to get the table names. That way, there's no worry about injection attacks, there's no information leak and internal and external naming are decoupled.

[rewegan]

I appreciate your resonspes, and I apologize if I have upset you.

I'm just a 17 year old kid in high school trying to get his software assignment done. I don't have a very strong computing vocabulary, and working with databases is very new to me. There really wasn't anything taught to us, and our only teacher during this process is google and forums. So I really have no understanding of what you just said. Don't worry about resonpding if I'm becoming frusturating, I understand what it's like dealing with an ignorant person.

Once again, thanks for the replies.

[misson]

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:

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:

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:

# 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:

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.

[sorahb]

Thanks for the help.

[rewegan]

Wow! That was a big help! Thanks a lot! I'll let you know if I need anything else.

Thanks again.