Home arrow Python arrow Page 5 - MySQL Connectivity With Python

A Different Species - Python

Python comes with a bunch of different modules that allow youto add new capabilities to your Python scripts. One of the more usefulones is the MySQLdb module, which allows you to execute SQL queries on aMySQL database through your Python application. This articledemonstrates basic usage of this module with simple examples andillustrations.

TABLE OF CONTENTS:
  1. MySQL Connectivity With Python
  2. Getting Started
  3. Animal Antics
  4. One By One
  5. A Different Species
  6. Be Prepared
  7. Endgame
By: icarus, (c) Melonfire
Rating: starstarstarstarstar / 179
August 21, 2002

print this article
SEARCH DEV SHED

TOOLS YOU CAN USE

advertisement
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())


 
 
>>> More Python Articles          >>> More By icarus, (c) Melonfire
 

blog comments powered by Disqus
escort Bursa Bursa escort Antalya eskort
   

PYTHON ARTICLES

- Python Big Data Company Gets DARPA Funding
- Python 32 Now Available
- Final Alpha for Python 3.2 is Released
- Python 3.1: String Formatting
- Python 3.1: Strings and Quotes
- Python 3.1: Programming Basics and Strings
- Tuples and Other Python Object Types
- The Dictionary Python Object Type
- String and List Python Object Types
- Introducing Python Object Types
- Mobile Programming using PyS60: Advanced UI ...
- Nested Functions in Python
- Python Parameters, Functions and Arguments
- Python Statements and Functions
- Statements and Iterators in Python

Developer Shed Affiliates

 


Dev Shed Tutorial Topics: