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