Python Language Database Access Accessing MySQL database using MySQLdb
Example
The first thing you need to do is create a connection to the database using the connect method. After that, you will need a cursor that will operate with that connection.
Use the execute method of the cursor to interact with the database, and every once in a while, commit the changes using the commit method of the connection object.
Once everything is done, don't forget to close the cursor and the connection.
Here is a Dbconnect class with everything you'll need.
import MySQLdb
class Dbconnect(object):
def __init__(self):
self.dbconection = MySQLdb.connect(host='host_example',
port=int('port_example'),
user='user_example',
passwd='pass_example',
db='schema_example')
self.dbcursor = self.dbconection.cursor()
def commit_db(self):
self.dbconection.commit()
def close_db(self):
self.dbcursor.close()
self.dbconection.close()
Interacting with the database is simple. After creating the object, just use the execute method.
db = Dbconnect()
db.dbcursor.execute('SELECT * FROM %s' % 'table_example')
If you want to call a stored procedure, use the following syntax. Note that the parameters list is optional.
db = Dbconnect()
db.callproc('stored_procedure_name', [parameters] )
After the query is done, you can access the results multiple ways. The cursor object is a generator that can fetch all the results or be looped.
results = db.dbcursor.fetchall()
for individual_row in results:
first_field = individual_row[0]
If you want a loop using directly the generator:
for individual_row in db.dbcursor:
first_field = individual_row[0]
If you want to commit changes to the database:
db.commit_db()
If you want to close the cursor and the connection:
db.close_db()
