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
If you want a loop using directly the generator:
for individual_row in db.dbcursor: first_field = individual_row
If you want to commit changes to the database:
If you want to close the cursor and the connection: