Difference between revisions of "Python:Databases"
Jump to navigation
Jump to search
m |
|||
(10 intermediate revisions by the same user not shown) | |||
Line 1: | Line 1: | ||
[[Category:Python]] | [[Category:Python]] | ||
+ | |||
+ | = MySQL Connector = | ||
See below for an example script | See below for an example script | ||
Line 7: | Line 9: | ||
;cnx = mysql.connector.connect(**cnxconfig) | ;cnx = mysql.connector.connect(**cnxconfig) | ||
− | :Open database as defined in the cnxconfig dictionary | + | :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() | ||
− | :Create a cursor that returns rows | + | :Create a cursor that returns rows as [[Python:DataTypes#Tuple | tuple]] |
;cursor = cnx.cursor(dictionary=True) | ;cursor = cnx.cursor(dictionary=True) | ||
− | :Create a cursor that returns rows | + | :Create a cursor that returns rows as [[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.<br> |
+ | :;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. | ||
+ | |||
<syntaxhighlight lang=python> | <syntaxhighlight lang=python> | ||
for row in cursor: | for row in cursor: | ||
Line 72: | Line 91: | ||
cursor.close() | cursor.close() | ||
cnx.close() | cnx.close() | ||
+ | </syntaxhighlight> | ||
+ | |||
+ | = ODBC connections = | ||
+ | |||
+ | <syntaxhighlight lang=python> | ||
+ | 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] | ||
+ | |||
+ | </syntaxhighlight> | ||
+ | |||
+ | Works with [[Pandas#Reading Data|pandas.read_sql]] too. | ||
+ | |||
+ | = MySqlAlchemy = | ||
+ | |||
+ | For more information check the sqlalchemy.org basic usage page[https://docs.sqlalchemy.org/en/11/core/connections.html#basic-usage]. | ||
+ | |||
+ | Very basic example for mysql with the pymysql module | ||
+ | <syntaxhighlight lang=python> | ||
+ | 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] | ||
+ | |||
</syntaxhighlight> | </syntaxhighlight> |
Revision as of 16:02, 23 May 2020
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 as tuple
- cursor = cnx.cursor(dictionary=True)
- Create a cursor that returns rows as 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]
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]