Python SQLAlchemy: A Tutorial – Built In

Random topic

We often encounter data as relational databases. We would generally need to write raw SQL queries, pass them to the database engine and parse the returned results as a normal array of records to work with them.
SQLAlchemy provides a “Pythonic” way of interacting with those databases. Rather than dealing with the differences between specific dialects of traditional SQL, such as MySQL, PostgreSQL or Oracle, you can leverage the Pythonic framework of SQLAlchemy to streamline your workflow and more efficiently query your data.
 
 
To start interacting with the database, we first need to establish a connection.
More on Data Science: These Python Scripts Will Help You Automate Your Data Analysis
 
SQLAlchemy can be used to automatically load tables from a database using reflection. Reflection is the process of reading the database and building the metadata based on that information.
For example:
 
Table and MetaData have already been imported. The metadata is available as metadata.
There are a couple functions to know, including:
We use .fetchmany() to load optimal no of rows and overcome memory issues in case of large datasets.
 
 
Let’s see some examples of raw SQLite Queries and queries using SQLAlchemy.
 
 
 
 
 
 
 
 
The case() expression accepts a list of conditions to match and the column to return if the condition matches, followed by an else_ if none of the conditions match. Use the cast() function to convert an expression to a particular type.
For example:
We use .scalar to the result when the result contains only a single value.
 
If you have two tables that already have an established relationship, you can automatically use that relationship by just adding the columns we want from each table to the select statement. Or you can do it manually.
For example, let’s start with importing the database:
More on Data Science: How to Use a Z-Table and Create Your Own
 
When you pass the database, which is not present, to the engine, SQLAlchemy automatically creates a new database.
 
 
 
Built In’s expert contributor network publishes thoughtful, solutions-oriented stories written by innovative tech professionals. It is the tech industry’s definitive destination for sharing compelling, first-person accounts of problem-solving on the road to innovation.

source

Leave a Reply

Your email address will not be published. Required fields are marked *