Create a slug from a name in MySQL/MariaDB

How to create a slug from the name of an item in MySQL/MariaDB. UPDATE my_table SET slug = LOWER(REGEXP_REPLACE(REPLACE(REPLACE(REPLACE(name, ' ', '-'), 'and', '-'), '[^a-zA-Z0-9-]', ''), '-+', '-'));

August 25, 2023 · 1 min · 28 words · AO

How to Join Multiple MySQL Tables in Python

First, you will need the mysql.connector. If you are unsure of how to get this setup, refer to How to Install MySQL Driver in Python. Presenting the data let’s take two (2) tables as a demonstration for the code below. Users – Table 1 { id: 1, name: 'Carl', fav: 254}, { id: 2, name: 'Emma', fav: 254}, { id: 3, name: 'John', fav: 255}, { id: 4, name: 'Hayley', fav:}, { id: 5, name: 'Andrew', fav:} Products – Table 2...

August 9, 2022 · 2 min · 316 words · Andrew

Retrieve records from MSSQLServer in Python

The below Python code will create a connection to a MSSQLServer instance, and retrieve data from it back into a variable called tblResults. # use pyodbc for database connection import pyodbc # keep our database credentials in a store secrets = { 'host': '<db_host>', 'Name': '<db_name>', 'username': '<db_username>', 'password': '<db_password>', } # create a connection string conn_str = f"DRIVER={{ODBC Driver 17 for SQL Server}};SERVER=tcp:{secrets['host']};DATABASE={secrets['Name']};UID={secrets['username']};PWD={secrets['password']}" # create a connection to the database conn = pyodbc....

August 2, 2021 · 1 min · 143 words · Andrew

How to Abort SQL statements after a set time in MariaDB

Sometimes you don’t want a SELECT query to run for more than a set amount of time. This is a roundabout way to make your server doesn’t die from slow-running queries. Obviously, you should tweak your database and potentially run your SELECT queries through an EXPLAIN plan first. This allows you to create appropriate indexes and find why things are slow in the first place. But: sometimes you still find yourself wanting to kill queries that may run over a certain amount of time....

July 17, 2021 · 1 min · 137 words · Andrew

SQL to Append all Fields if Shorter than a set Length

This came in very handy for me at one point because I needed to have a certain field in each row 14 characters long, but half of them were only 12 characters long. In MySQL: update `table` set `field` = CONCAT(`field`, '1') where LEN(`field`) = 13; In MSSQL: update `table` set `field` = CONCAT(`field`, '1') where LENGTH(`field`) = 13;

June 8, 2020 · 1 min · 59 words · Andrew

Get the Second Highest Salary in MySQL

The problem Write a SQL query to get the second highest salary from the Employee table. +----+--------+ | Id | Salary | +----+--------+ | 1 | 100 | | 2 | 200 | | 3 | 300 | +----+--------+ For example, given the above Employee table, the query should return 200 as the second highest salary. If there is no second highest salary, then the query should return null. +---------------------+ | SecondHighestSalary | +---------------------+ | 200 | +---------------------+ The solution SELECT DISTINCT Salary AS SecondHighestSalary FROM Employee ORDER BY Salary DESC LIMIT 1 OFFSET 1 The output {"headers": ["SecondHighestSalary"], "values": [[200]]}

June 1, 2020 · 1 min · 101 words · Andrew

How to Purge the BinLog for MySQL and MariaDB

If you see lots of disk space quickly disappearing on your MySQL or MariaDB server, then you may want to look into the BinLog directory, it is located at /var/log/mysql/. In my case I saw this: usr@server-1:/var/log/mysql# ll total 5571044 drwxr-s--- 2 x x 4096 Mar 25 18:18 ./ drwxrwxr-x 12 x x 4096 Mar 25 05:25 ../ -rw-rw---- 1 x x 104857748 Mar 23 09:29 mariadb-bin.000301 -rw-rw---- 1 x x 104859422 Mar 23 10:07 mariadb-bin....

March 27, 2020 · 2 min · 359 words · Andrew

How to Delete from a Table where rows match in Another Table – MySQL

So you have a table where you want to delete a bunch of rows, based on a particular column being matched in another table. This is easily achievable with MySQL. DELETE FROM TABLE1 WHERE domain IN ( SELECT domain FROM TABLE2 ) The above SQL will delete all rows in TABLE1 that are found in TABLE2. But what if we need to limit which records are returned from TABLE2? That’s simple too....

March 24, 2020 · 1 min · 103 words · Andrew

REPLACE INTO instead of INSERT INTO – MySQL

There are many occasions where you need to INSERT a record into a MySQL database table, but the record already exists. INSERT INTO queue_domains (domain) VALUES('statvoo.com') This will result in a Duplicate entry 'statvoo.com' for key 'domain' error A nice and simple way to resolve this is to use REPLACE INTO instead. This will look to see if the item already exists and will simply replace it. This is faster than doing a SELECT followed by an INSERT....

March 23, 2020 · 1 min · 130 words · Andrew

A bit on SQL Standards

SQL (Structured Query Language) for relational databases was maintained and standardised by the American National Standards Institute (ANSI) and the International Organisation for Standardisation (ISO) which is affiliated with the International Electrotechnical Commission (IEC). When a new SQL standard is published by these organisations it is outlined on the ISO website and available for everyone to both reference and use as required. The current version used in most production instances is commonly ISO/IEC 9075-11:2011; however, in December of 2016 a new version was published and is described under ISO/IEC 9075-11:2016 which can be found here: https://www....

November 12, 2017 · 3 min · 567 words · Andrew