Loading...python-slither-out-of-your-control-how-to-load-data-science-languages-in-and-out-of-our-relational-database">Don’t let Python slither out of your control – how to load Loading...data science languages in and out of our relational database
A recent study of different job listings revealed which data science skills are most in demand for data scientists. It was no surprise that over 70% of all listings highlighted knowing Python as being a key technical skill. So, this got me thinking about what this means for you, when you’re working with data science languages with your relational database. In this blog, I’ll show you how easy it is for your team can to extract or load data into the database with their chosen programming language. As well as demonstrating why some methods are perhaps not quite as ‘optimal’.
How to connect Python to our relational database
There are several ways to read data from our relational database and write it back with the help of Python. Here’s a quick snapshot of the pros and cons of three common connection methods.
Our Python 2 package
We have a Python 2 package, which acts as a wrapper for the PyODBC package. You’re able to read data from the database directly into a Pandas data frame and also write data from a data frame into the database. This package only works with Python 2. And it’s worth nothing that with the upcoming support for Python2, this package is becoming more and more obsolete. You can find this package at the Exasol GitHub.
Websocket API
For Python 3, we offer a websocket API package. This package uses our Websocket API and doesn’t need any driver manager. Unfortunately, you’re not able to load data directly into an Pandas data frame and the performance isn’t the best for large data sets, but it’s fine when you only have a limited amount of data. It can also be found at the Exasol GitHub.
Badoo pyexasol package
The second fastest option for Python 3 is the Pyexaol package of Badoo. The package is now an official fork in the Exasol GitHub repository. It gives you the possibility to write into or out of a Pandas data frame, so it’s very flexible. And it’s available for Python 3. Additionally, there is handy manual with many example on how to use it. As this is what I’d call the go-to package, this rest of this blog will focus on its main functionality.
Install pyexasol package
Installing the Python package alone isn’t enough. There are several required and optional dependencies.The following core dependencies are all explained in detail in the manual – and are well worth a read :
- Exasol >= 6
- Python >= 3.6
- websocket client >= 0.47
- rsa
It’s also recommended to use the Pyexasol package together with Pandas, as it simplifies the handling of huge data sets. The following command has to be executed to install Pyexasol and Pandas in your Python environment:
pip install pyexasol[pandas]Execute SQL statements
I’ll explain the basic functionality of this package with a little example. Imagine I created an empty test table with a timestamp column, and when the current timestamp has been selected I wrote it back into this table.
To do this first you have to create a connection to our database:
Con = pyexasol.connect(dsn=[IP-adress]:[port], user=[user], password = [password], schema = [schema], compression=True)Executing a single query can be done with the execute() function of the connection object. This function returns an ExaStatement object, which could also be used to iterate over a result set of a SQL statement.
Con.execute("create table sandbox.tbl_timestamp (cur_time timestamp) ")Reading data from our relational database
It’s really intuitive and easy to export data into a pandas data frame when reading data from our database. The pyexasol package offers the Export_to_pandas() function, which returns a pandas data frame with the resulting code set passed as the following SQL statement:
data_frame = Con.export_to_pandas('select systimestamp')print(data_frame.head())Write data into our relational database
Writing data back into our relational database is as easy as reading data. You just have to use the import_to_pandas() function and provide the data frame and the target table. To do this you need to make sure the target table is located in the schema, and that you mention it as a parameter when you connect it to our database.
Con.import_from_pandas(data_frame, 'TBL_TIMESTAMP')After this, you should close the existing connection.
Con.closeAnd the winning method is? Go direct into Pandas
As we’ve seen there are several ways to access our database through Python. But writing data directly into a Pandas data frame is highly recommended, as the data frames are easy to handle. And it can be used as an input for a TensorFlow model or other Loading...machine learning methods.