Python:Databases

See below for an example script

import mysql.connector

Load module for mysql

cnx = mysql.connector.connect(**cnxconfig)

Open database as defined in the cnxconfig dictionary. The ** passes the dictionary unpacked to a function.

cursor = cnx.cursor()

Create a cursor that returns rows in a tuple

cursor = cnx.cursor(dictionary=True)

Create a cursor that returns rows in a dictionary { column1: value1, column2: value2 }

cursor.execute(query)

Execute 'query'. With a tuple cursor columnames are in cursor.column_names. Traverse the cursor to fetch the data. The cursor returns a list for each row.

for row in cursor:
 print(row)
 for column in row:
 	print(column)

cursor.close()

Close the cursor

cnx.close()

Close the database connection

Code example showing all:

import mysql.connector 

cnxconfig = {
    'host': 'hostname',
    'port': 3306,
    'user': 'username',
    'password': '',
    'database': 'DBname'
}

#cnx = mysql.connector.connect(host='hostname', port=3306, user='username', database='DBname')
cnx = mysql.connector.connect(**cnxconfig)

if cnx:
	print("Connection, continue")
else:
	print("No connection, exit")
	exit()

print("Tuple cursor")	
cursor = cnx.cursor()

query = ("SELECT * FROM table")
 
cursor.execute(query)
for columname in cursor.column_names:
 print(columname)
for row in cursor:
 print(row)
 for column in row:
 	print(column)

print("Dictionary cursor")
cursor = cnx.cursor(dictionary=True)
cursor.execute(query)
for row in cursor:
	for columname in row.keys():
		print("{0:25s}: {1:}".format(columname, row[columname])) 

cursor.close()
cnx.close()