Introducing SQL Cells and Database Connections to Datalore Notebooks!
Greetings from the Datalore team!
What are the most common ways to query SQL databases from a Python notebook? They might include using Python SQL libraries such as PySQL or SQLAlchemy, or creating a script in a separate SQL IDE and importing the results to your notebook as a CSV.
We can now suggest a more effective method: combine native SQL cells and Python code in Datalore notebooks.
Datalore Professional and Enterprise users get unlimited access to this feature. Community plan users can try it completely free for 30-days!
Read this blog post to learn how you can query your SQL databases from Datalore’s editor UI.
Step 1: Connect to an SQL database
Instead of writing boilerplate Python code to connect to a database, you can now create a connection that you can then reuse in multiple notebooks with a single click.
To connect a database follow the steps in this gif:
Datalore supports user and password authentication for almost all possible databases, including PostgreSQL, Snowflake, MySQL, MariaDB, Amazon Redshift, Oracle, Azure SQL Database, and many others.
After connecting to a database once you’ll be able to browse it’s schema and connect to other notebooks with one click.
We also created a Demo database and everyone on Community and Professional plans can connect to it from the “Take a tour” tip.
You can also connect to a demo database by explicitly specifying these credentials:
- DB type: PostgreSQL
- Host: demo-database.private.datalore.io
- User, Password, Database: datalore
Under the hood, this feature is powered by JetBrains DataGrip, which is a professional tool for SQL databases. It has allowed us to reuse a lot of code that has already proven itself in production and add support for many databases with different SQL dialects. Also running a connection to a database outside of the notebook kernel has brought another benefit – additional security: your database passwords are never exposed to your collaborators.
Can’t connect to your databases from the public cloud tools? Consider installing Datalore privately on-premises.
Step 2: Now it’s time to add an SQL cell
After creating a database connection, you can attach it to any notebook and query the data using SQL cells.
When writing SQL code you will get code completion based on the introspected database tables and syntax highlighting. The result of your query will be automatically saved in a pandas DataFrame and you can seamlessly continue your data analysis with Python.
Step 3: Collaborate with your team!
In Datalore you can collaborate on notebooks in real-time, and SQL cells are no exception here. You can simultaneously edit SQL code with your teammates and create queries together. Sharing a notebook is simple – just send a public edit/view access link or invite collaborators by email.
Step 4: Visualize results and share reports
After executing an SQL cell you can visualize the results right away using the Visualize tab or Chart cells. Furthermore, you can add interactive controls and turn your notebooks into interactive or static reports!
Interactive reports are available for Datalore Enterprise users only.
Learn about Datalore Enterprise
FAQ
– Is it a Pro feature?
Yes, SQL cells and database connections is a Pro feature. However, Community users can try it free for 1 month.
– I’m a Community plan user, what will happen after the trial month?
To continue working with SQL cells you will need to upgrade to Datalore Professional.
– What if I am not allowed to connect my databases to cloud tools?
For companies that need to host their data privately on-premises, we offer the Datalore Enterprise plan. Learn more here.
– You don’t support the authentication option that I use, what can I do?
Please let us know which authentication options you need on our forum, as we plan to support more options going forward.
And last but not least, you can watch this short video tutorial for tips on mastering SQL cells in Datalore and see the feature in action.
That’s all for now. Follow us on Twitter @JBDatalore to hear about our updates!
Happy analyzing!
The Datalore team
Subscribe to Datalore News and Updates
