Difference between revisions of "Python:Databases"
Jump to navigation
Jump to search
Line 10: | Line 10: | ||
;cursor = cnx.cursor() | ;cursor = cnx.cursor() | ||
− | :Create a cursor that returns rows in a tuple | + | :Create a cursor that returns rows in a [[Python:DataTypes#Tuple | tuple]] |
;cursor = cnx.cursor(dictionary=True) | ;cursor = cnx.cursor(dictionary=True) | ||
− | :Create a cursor that returns rows in a dictionary { column1: value1, column2: value2 } | + | :Create a cursor that returns rows in a [[Python:DataTypes#Dictionary_or_dict | dictionary]] { column1: value1, column2: value2 } |
;cursor.execute(query) | ;cursor.execute(query) | ||
− | :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. |
<syntaxhighlight lang=python> | <syntaxhighlight lang=python> | ||
for row in cursor: | for row in cursor: |
Revision as of 17:30, 1 March 2019
See below for an example script
- import mysql.connector
- Load module for mysql
- cnx = mysql.connector.connect(**cnxconfig)
- Open database as defined in the cnxconfig dictionary. The ** passes the dictionary unpacked to a function.
- 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.
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()