Inserting Variable Data with MySQL

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."

mysql tutorials

Next, head to the "Files" tab, and create a new file by entering the title and clicking on "new."

mysql tutorials

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

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:

mysql tutorials

Breakdown of each element of the script, click to learn more:

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.

There exists 5 challenge(s) for this tutorial. for access to these, video downloads, and no ads.

There exists 10 quiz/question(s) for this tutorial. for access to these, video downloads, and no ads.

The next tutorial:

  • Intro to MySQL
  • Creating Tables and Inserting Data with MySQL
  • Update, Select, and Delete with MySQL
  • Inserting Variable Data with MySQL
  • Streaming Tweets from Twitter to Database