In this screencast, will explore different ways to access data from your codeigniter models. We’ll start by writing SQL from scratch and refactor our models until our code is safe against sql injection, database agnostic, and DRY. We’ll wrap up the screencast by taking a quick look at PHP ActiveRecord.
I’ve been playing around with CodeIgniter in my spare time and decided to release one of my projects into the wild. QuizMotor is a simple app to flow through multiple choice question as if you were taking a quiz. As of now the questions are about CodeIgniter, PHP, HTML, etc, but I’m hoping that it will grow to cover a variety of subjects. Check it out: http://www.quizmotor.com
In this article, we’ll write a simple Python script that will query a DB2 database and print the results. To link Python and DB2, we’ll use the PyDB2 interface. To install PyDB2 download a copy and follow the instructions in the README file.
#!/usr/bin/env python import DB2 # Instantiate connection object and connect to DB2 database db = DB2.connect('dbname', 'username', 'password') # Instantiate cursor object cursor = db.cursor() # Build sql statement using python's multiline quotation syntax sql = """select address_line_1 , address_line_2, city, state, zip from property""" try: # Execute statement and fetch results cursor.execute(sql) results = cursor.fetchall() # Loop through rows and print all columns for row in results: print row[0:] except: print "Error: Unable to fetch data from DB2" # Close database connection db.close()
For details on getting Python and MySQL wired, check out the previous post. This post is about performing create, update, and delete operations against a MySQL database from within a Python script. First, let’s create a new row in the property table of a database named iRent:
#!/usr/bin/env python import MySQLdb # Instantiate connection object and connect to MySQL database db = MySQLdb.connect("localhost", "username", "password", "irent") # Instantiate cursor object cursor = db.cursor() # Prepare a dml statement sql = "insert into property (address_line_1, user_id, type_id) \ values ('%s', '%d', '%d')" % ('234 Sathorn Rd', 1, 1) try: # Execute dml and commit changes cursor.execute(sql) db.commit() except: # Rollback changes db.rollback() # Close database connection db.close()
Here’s a few simple templates for performing basic crud operations using Python and MySQL. The snippets below lean on the MySQLdb interface, which exposes the database API to Python. To install MySQLdb download a copy and follow the instructions in the README file. To get this working on Mac OSX, I needed to add the following export statement to my .bash_profile:
The steps required for database access, regardless of language and dbms, are connect to the database, build a sql or dml statement, execute the statement, process the results, disconnect from the database. As you’ll see below, linking up Python and MySQL is no different:
This is the final chapter of the Simple Table tutorial. In this chapter we’ll be adding a few visual flourishes to the application using jQuery. We’ll be adding a client-side data filter, a pagination control, dynamic table sizing, and more.
This is part 4 of the Simple Table tutorial. In this chapter we’ll be styling the application using CSS. If you’ll be following along, I strongly suggest watching the original videos hosted on vimeo as the video is being downsampled when played directly from wordpress. To get to the original videos simply click on the word “Vimeo” in the bottom right hand corner of each clip.