Python:Databases

MySQL Connector

See below for an example script

import mysql.connector

Load module for mysql

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

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

import cx_Oracle

Load module for oracle

cnx = cx_Oracle.connect(user=dbuser, password=dbpasswd, dsn=dbtns_alias)

Open Oracle database.

cnx.commit()

Commit all open transactions to the database

cursor = cnx.cursor()

Create a cursor that returns rows as tuple

cursor = cnx.cursor(dictionary=True)

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

cursor = cnx.cursor(buffered=True)

Create a cursor that retrieves the complete result at once so you can check rowcount immediately. Beware of overloading your memory if you expect large results.

cursor.execute(query)

Execute 'query'. Traverse the cursor to fetch the data. The cursor returns a tuple for each row by default.

cursor.rowcount

The number of rows affected by this query.

cursor.column_names

The columnnames

cursor.fetchone()

Return the next row from the cursor result.

cursor.fetchmany(size=2)

Return the first two rows of the result in a list.

cursor.fetchall()

Return all (remaining) rows of the result in a list.

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()

ODBC connections

import pyodbc

dsn = 'DSN=<name of ODBC-DNS>'

cnx = pyodbc.connect(dsn,autocommit=True)
cursor = cnx.cursor()

query = 'show databases'

result = cursor.execute(query)
columnnames = [e[0] for e in cursor.description]
for row in result:
    field1 = row[0]

Works with pandas.read_sql too.

MySqlAlchemy

For more information check the sqlalchemy.org basic usage page[1].

Very basic example for mysql with the pymysql module

import pymysql
import sqlalchemy

dbconnect = 'mysql+pymysql://user:password@databaseserver/databasename'

engine = sqlalchemy.create_engine(dbconnect)

query = 'select something from table'
result = engine.execute(query)
for row in result:
    field1 = row[0]