Python:Databases

From wiki
Jump to: navigation, search


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]

Wotks 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]