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 fetched (SELECT) or affected (INSERT,UPDATE) 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()

mariadb

The mariadb module[1][2] works almost the same as the mysql.connecctor above.

import mariadb

# Connection parameters
config = {
    'host': 'xxx',
    'port': 3306,
    'user': 'user',
    'password': 'password',
    'database': 'db' 
}

# Connect to MariaDB
conn = mariadb.connect(**config)
cursor = conn.cursor()
cursor.execute(<query>)
for row in cursor:
    print(row)

The rows are tuples with a counter (id) as first element. 'dictionary': True in the connection config should return dictionaries

cursor.description

Spec of the tuples in cursor

cursor.metadata['field']

The fields in the cursor

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[3].

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]