MySQL Connectivity With Python - A Different Species (
Page 5 of 7 )
Obviously, you can also perform INSERT, UPDATE and DELETE queries via the MySQLdb module. Consider the following example, which illustrates:
#!/usr/bin/python
# import MySQL module
import MySQLdb
# connect
db = MySQLdb.connect(host="localhost", user="joe", passwd="secret",
db="db56a")
# create a cursor
cursor = db.cursor()
# execute SQL statement
cursor.execute("""INSERT INTO animals (name, species) VALUES ("Harry",
"Hamster")""")
You can modify this so that the values for the query string are input by the user - take a look at this variant of the example above, which demonstrates:
#!/usr/bin/python
# import MySQL module
import MySQLdb
# get user input
name = raw_input("Please enter a name: ")
species = raw_input("Please enter a species: ")
# connect
db = MySQLdb.connect(host="localhost", user="joe", passwd="secret",
db="db56a")
# create a cursor
cursor = db.cursor()
# execute SQL statement
cursor.execute("INSERT INTO animals (name, species) VALUES (%s, %s)",
(name, species))
This time, when you run the script, you'll be asked for the values to be inserted into the database.
Please enter a name: Rollo
Please enter a species: Rat
Notice the manner in which variables have been integrated into the SQL query in the example above. The %s placeholder is used to represent each variable in the query string, with the actual values stored in a tuple and passed as second argument.
In case you have auto-increment fields in your database, you can use the cursor object's insert_id() method to obtain the ID of the last inserted record - this comes in handy when you're dealing with linked tables in an RDBMS, as newly-inserted IDs from one table often serve as keys into other tables. The following code snippet should demonstrate how this works:
#!/usr/bin/python
# import MySQL module
import MySQLdb
# connect
db = MySQLdb.connect(host="localhost", user="joe", passwd="secret",
db="db56a")
# create a cursor
cursor = db.cursor()
# execute SQL statement
cursor.execute("""INSERT INTO test (field1, field2) VALUES ("val1",
"val2")""")
# get ID of last inserted record
print "ID of inserted record is ", int(cursor.insert_id())
