Python:Databases
Jump to navigation
Jump to search
The printable version is no longer supported and may have rendering errors. Please update your browser bookmarks and please use the default browser print function instead.
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]