Oracle to the power of Python!
2020-11-20Every DBA is doomed to make some scripts and with enough time, they will increase in numbers like thousands if not tens of thousands. As I stated in my homepage, we were DevOps years before that acronym born, so we are pretty much comfortable with scripts.
Python is a great and super easy language to learn. It can do pretty much anything and Python is probably the best language for scripting (Ansible is Python!).
Oracle itself develop a set of libraries to interact with Oracle and, you know what?, they are super cool! They are called cx_Oracle and you can find them here.
Let me show you a simple snippet and its power:
import cx_Oracle query = "SELECT TABLE_NAME, TABLESPACE_NAME, STATUS FROM USER_TABLES ORDER BY 1" dsn = cx_Oracle.makedsn(host=hostname, port=port, service_name=db_name) connection = cx_Oracle.connect(dsn=dsn, user=username, password=password) cursor = connection.cursor() cursor.execute(query) for row in cursor.fetchall(): print("Table Name : " + str(row[0])) print("Tablespace Name: " + str(row[1])) print("Status : " + str(row[2])) cursor.close() connection.close()
The above code connect to a database (using hostname, port and service name) with a username and a password, setup a cursor and execute it, fetching all the rows in a loop and printing it. At the end, cursor and connection will be closed.
The above is only the tip of the iceberg of course, but it is really as simple as that. Now, imagine how can you make all your scripts to the power of Python!
Happy coding! 🙂