Sql
-
Deal with Common Types of SQLAlchemy Exceptions for Running SQL Queries in Python
When working with databases using SQLAlchemy, many developers may simply catch the base exception SQLAlchemyError, or worse, the base Python Exception. This is not a good practice because it makes the code work like a black box and we cannot anticipate what types of exceptions can be raised. In this post, we will introduce some… Continue reading
-
Understand SQL Injection and Learn to Avoid It in Python with SQLAlchemy
SQL Injection is one of the most common and also most dangerous web security vulnerabilities which allows hackers to inject malicious SQL code into unvalidated and unsanitized plain SQL queries. It is also a common issue that new developers overlook. The cause and solution for SQL Injection are actually pretty simple. In this post, we… Continue reading
-
Use Triggers and Audit/Log Tables to Track Table Changes in MySQL
In MySQL and any relational databases, a trigger is some code that is executed automatically when some event is fired. Depending on the event, there are triggers that are run before or after a table row is inserted, updated, or deleted. In this post, we will introduce how to use triggers to keep track of… Continue reading
-
Important MySQL Data Definition Language (DDL) commands we should know for managing our tables
As a data engineer, checking and updating the schemas of tables is our bread and butter. There are plenty of tutorials online already but few of them focus on the conventions that should be followed. SQL is very flexible and can work in a “robust” way. You can use both lowercase and uppercase queries and… Continue reading
-
How to Calculate Medians with Grouping in MySQL
Calculating the median of an array of data is pretty straightforward in any programming language, even in Excel, where a built-in or third-party median function can be used directly. However, in MySQL, the median function is not natively supported. To get the median, we need to write some smart queries with subqueries. In this post,… Continue reading
-
How to Perform Bulk Inserts With SQLAlchemy Efficiently in Python
It’s very convenient to use SQLAlchemy to interact with relational databases with plain SQL queries or object-relational mappers (ORM). However, when it comes to bulk inserts, namely, inserting a large number of records into a table, we may often have performance issues. In this post, we will introduce different ways for bulk inserts and compare… Continue reading
-
How to Connect to GCP Cloud SQL Instances in Cloud Run Services
If you use the Google Cloud Platform (GCP), it’s common to access Cloud SQL in your Cloud Run services. This is supposed to be an easy task because both Cloud SQL and Cloud Run are in the same network. However, when you do it yourself, you may not be able to do it successfully in… Continue reading
-
Some Tips for Using DBeaver — A Universal Database Tool
DBeaver is a universal database administration tool that can be used to manage all kinds of relational and NoSQL databases. DBeaver has both a community edition (CE) which is free and open-source and a commercial enterprise edition (EE). The community edition supports all kinds of relational databases such as MySQL, PostgreSQL, Oracle, etc. The enterprise… Continue reading
-
How to work with JSON data in MySQL
MySQL supports a native JSON data type that supports automatic validation and optimized storage and access of the JSON documents. Although JSON data should preferably be stored in a NoSQL database such as MongoDB, you may still encounter tables with JSON data from time to time. In the first section of this post, we will… Continue reading
-
How to Execute Plain SQL Queries With SQLAlchemy in Python
There can be many cases where you don’t want to use the advanced Object Relational Mapper (ORM) features of SQLAlchemy and just want to execute plain SQL queries. This is common for legacy systems where plain queries are used everywhere. Besides, for data analysis-oriented projects, it’s also more common and more convenient to use plain… Continue reading
SuperDataMiner 