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.

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'. Columnames are in the tuple cursor.column_names. Traverse the cursor to fetch the data. The cursor returns a tuple for each row.

cursor.rowcount

The number of rows affected by this query.

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
import sqlalchemy

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

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

query = 'show databases'

result = cursor.execute(query)
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]