Dynamic MySQL CREATE TABLE statement with pandas and pyodbc

Have you ever had to type out a massive CREATE TABLE statement by hand? One with dozens of columns? Maybe several dozens of columns? There are likely some GUI tools to help with large CREATE TABLE commands. Or, other drag-n-drop types of software that I am not familiar with. What if you could write a few lines of Python code and take care of a huge CREATE TABLE statement with way less effort than typed manually? Interested? Continue reading and see how using pandas, pyodbc, and MySQL…

[Keep reading for more MySQL database and Python/PHP-centric content >>>]
Advertisements

3 MySQL Commands Developers should know.

If you are a developer working in a MySQL environment, this blog post is for you. I share 3 MySQL commands or statements that you should know. That is a bold statement, I know. Turns out, once you do know (of) these commands, you will use them all the time. They minimize guesswork which leads to better productivity in other facets of your programming and querying workflow. I use them myself almost daily and am sure you will too once you see how simple they are to use. So why should you know them? Continue reading and find out…

[Keep reading for more MySQL database and Python/PHP-centric content >>>]

TRIM() string function in MySQL – with examples.

In this post, I’ll cover examples of the MySQL TRIM() function. TRIM() removes specific characters – or spaces – from a given string, at either: the beginning, ending, or potentially in both locations depending on several factors. With an optional keyword argument that controls which character(s) – if any – are removed, TRIM() can be tricky so let’s gain understanding with several easy-to-digest examples…

[Keep reading for more MySQL database and Python/PHP-centric content >>>]

Sorting associative arrays in PHP with array_multisort() – New learning

If you write MySQL queries, at some point you are going to have to provide query results in a specific order. To impose any ordering in MySQL (this applies to SQL overall and is not directed only at MySQL), you have to use the ORDER BY clause. Without it, there is no guaranteed order. The database is free to send back query results in any order. As I learn PHP, I make it a point to explore both the MySQL side, along with the PHP side in regards to similar type tasks and the efficiency of each. So far in my PHP journey, I have found that arrays are used quite extensively. In this post, I’ll cover array_multisort() – one of many in-built PHP functions – used for sorting arrays. In the context of the example data for this post, the arrays are populated by an unordered MySQL query. Let’s see one example of how you can establish a sorting order in a PHP array, that is populated from an unordered MySQL query…

[Keep reading for more SQL database and Python/PHP-centric content >>>]

Exploring .count() and COUNT() – MySQL Shell Python mode

Retrieving a table count of rows using the COUNT(*) aggregate function in MySQL is a straight-forward query. What if I told you that with the MySQL Shell, there are actually 3 possible ways – between 2 Shell modes (\sql and \py) – to retrieve a table row count? Would you be interested in knowing about them? Honestly, one of the queries will not surprise you in the least bit, as you are likely already using it. However, the other 2 queries – in this context – are specific to MySQL Shell Python mode. You can likely execute these queries in Javascript Mode. However, I am not versed in Javascript programming nor MySQL Shell Javascript mode so those queries are not covered here. Continue reading to see the example queries in Python mode…

[Keep reading for more MySQL database and Python/PHP-centric content >>>]