Difference between revisions of "Python:Databases"

From wiki
Jump to: navigation, search
Line 7: Line 7:
  
 
;cnx = mysql.connector.connect(**cnxconfig)
 
;cnx = mysql.connector.connect(**cnxconfig)
:Open database as defined in the cnxconfig dictionary. The ** passes the dictionary unpacked to a function.
+
: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()
 
;cursor = cnx.cursor()

Revision as of 11:03, 18 September 2019


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