Often times, you need some form of ordering in a result set. In the SQL world, without an ORDER BY clause, query results order is not guaranteed. What if you are working in the pandas world? Fear not. You can order DataFrame results in a similar fashion as that of ORDER BY using the sort_values() function. Let’s learn together by example…
Regular expressions are somewhat new to me in the sense of, I know what they are, it’s just I have never had to use them. I’ve always gotten by using the LIKE search pattern. However, I wanted to replicate a particular UPDATE in PostgreSQL that I carried-out in MS Access (not with regex’s there either) and discovered a need for basic regex search and match in order to accomplish it. Let’s visit and learn about the regexp_match() function and how I used it…
I have written several articles recently, about pandas and PostgreSQL database interaction – specifically in loading CSV data. In this post, I’ll cover what I have recently learned using pandas merge() and read_sql_query(), retrieving query results using INNER JOIN‘s and similar queries.
In my day job (Pipeline Survey Data Analyst) I sometimes have the opportunity to write custom SQL queries in an MS Access database, which is the back end of one of the proprietary GIS solutions, my colleagues use. Although I feel that Access’s SQL implementation is not quite as robust as other SQL dialects, it does get the job done in certain situations (the visual interface continues to grow on me). For a learning experiment, I decided to reproduce – and solve – the same requirements using PostgreSQL, as that I had in the MS Access environment. However, I discovered an all-together different challenge.
Both MS Access and MySQL provide several string functions. One of those is a particularly useful string comparing function. MySQL has STRCMP() while in Access, there is a similar StrComp(). Postgres does not have its own version that I am aware of. I leaned heavily on this type of function in MS Access. Hopefully, readers will clue me in on what Postgres-specific string function I could use in its stead. Meanwhile, read on to see the workaround I used…
Recently, I wrote a post about inserting pandas DataFrame values into a PostgreSQL table, using SQLAlchemy data types to type cast them to the desired data type. In this post, I’ll explore – and leverage – pandas.concat() and get a two for one by merging 2 pandas DataFrames into 1, prior to uploading.
[Head this way for great PostgresSQL blogging >>>]On this website we use first or third-party tools that store small files (cookie) on your device. Cookies are normally used to allow the site to run properly (technical cookies), to generate navigation usage reports (statistics cookies) and to suitable advertise our services/products (profiling cookies). We can directly use technical cookies, but you have the right to choose whether or not to enable statistical and profiling cookies. Enabling these cookies, you help us to offer you a better experience.