Python Mysql multi handle 2.7.2

07.16.2022

Real world use case for multiple instantiations of python classes, Use a Python Class more than once

At the beginning of our python program, we normally would import the library for MySql operations;

import MySQLdb

Using this library, we then use .connect() and .cursor but because we may be using the same database repeatedly, here is an alternate method of initializing our connection. First, we create a file called credentials.py in our working directory to initialize the database connection;

#!/usr/bin/python
import MySQLdb
def dbconn():
  server = 'localhost'
  user = 'MySql_user_name'
  password = 'MySql_password'
  dbase = 'MySql_database_name'
  return MySQLdb.connect(server,user,password,dbase)

This means that I can quickly connect to the same database, everytime I write a new Python program, substituting the new library for making the connection.

import credentials
handle = credentials.dbconn();

We now can look at some table rows with only four more statements;

cursor=handle.cursor()
sql="SELECT * FROM table_name WHERE 1=1"
cursor.execute(sql)
rowArray=cursor.fetchone()

Add on more statement, like ‘print rowArray’ or do whatever you need to do to look at the row. You probably will write a loop that encloses the last statement.

Just to take another approach, let’s look at our MySql Python connect without the separate credentials function;

#!/usr/bin/python
import MySQLdb
server = 'localhost'
user = 'MySql_user_name'
password = 'MySql_password'
dbase = 'MySql_database_name'
handle = MySQLdb.connect(server,user,password,dbase)

cursor=handle.cursor()
sql="SELECT * FROM table_name WHERE 1=1"
cursor.execute(sql)
rowArray=cursor.fetchone()

print rowArray

And with the credentials library;

#!/usr/bin/python
import credentials
handle=credentials.dbconn()
cursor=handle.cursor()
sql="SELECT * FROM table_name WHERE 1=1"
cursor.execute(sql)
rowArray=cursor.fetchone()

print rowArray

Next an example using your credentials.py to make a python program to read two tables,first we get the user record, and then their posts.

import credentials
userHandle=credentials.dbconn()
uSql="SELECT * FROM jos_users WHERE id=57"
cursor=userHandle.cursor()
cursor.execute(sql)
uArr=cursor.fetchone
contHandle=credentials.dbconn()
cSql="SELECT * FROM jos_content WHERE created_by=57"
cCursor=contHandle.cCursor()
cCursor.execute(cSql)
for i in cCursor.fetchone
  print i['title']

Python2