Now we're ready to talk about inserting variable data. So far, we've been running all of our queries "statically," or via "hard-coding." The term "hard-coding" is used to describe scenarios where the code should contain variables, but instead the variables are coded by hand rather than by program.
If using PythonAnywhere:
If you are following along via PythonAnywhere, let's go ahead and kill the running MySQL console. Click on the "Consoles" tab, then use the "kill" option to the right of the console in the list under "Your consoles."
Next, head to the "Files" tab, and create a new file by entering the title and clicking on "new."
Via your own Server:
In order to interact with a MySQL database, you're going to need the MySQLdb module. To get that, you can either use pip, yum or apt-get.
sudo apt-get install python-mysqldb
yum install python-mysqldb
pip install MySQL-python
Once you have that, do:
sudo nano dbconnectex.py
Now we're ready to interact with MySQL via our Python file.
import MySQLdb # replace mysql.server with "localhost" if you are running via your own server! # server MySQL username MySQL pass Database name. conn = MySQLdb.connect("mysql.server","beginneraccount","cookies","beginneraccount$tutorial") c = conn.cursor() c.execute("SELECT * FROM taula") rows = c.fetchall() for eachRow in rows: print eachRow
Running that, you should get the following output:
Breakdown of each element of the script, click to learn more:import MySQLdb
We import MySQLdb, which is a Python module for interacting with MySQL databases and the MySQL language.
Next, we connect to our database using MySQLdb.connect(), which takes the server address, the MySQL username, the MySQL password, and then the database name.
We then assign to the variable of "c," short for "cursor," the conn.cursor(). Think of the cursor like your mouse cursor. The cursor is used for actions like selecting things, moving around... etc. You will use it to execute all of your queries.
Now we can use c.execute() to literally execute queries, which you're familiar with by now. Here, we're issuing a SELECT * statement, which is going to return to us all of the rows within the taula table.
Now we use c.fetchall(). So, we're using the cursor to fetch all of the returned rows, so now "rows" is basically a list of the return.
Finally, we iterate through our "rows" list with a for loop.