Master SQL with Python: Lesson 12 - Maintaining Data in PostgreSQL using Upsert

Having current data is critical for data analysis but how do you keep data up to date? In this video, you will learn how to insert, update, and delete rows from PostgreSQL tables to keep them current. We will use the Upsert statement to do this in the most efficient manner possible.

Content available at:

Video: Using SQL with Python: Lesson 8 - Introducing PostgreSQL

Video: Using SQL with Python: Lesson 9 - Using PostgreSQL for Data Analysis

[camilotorres]

You don't know how much you saved me! Thankyou very much

[lastdance]

This is by far the most practical and elaborated free lecture I have ever seen online. Thank you Bryan, the way you presented made it really easy for beginners to understand! I am almost finishing your Master SQL with Python series and will certainly continue with your Databrick series upon completion. I have a small favor to ask though, could you please do a DW upsert video (preferably still in PostgresQL and/or Sqlite) with respect to Slowly Changing Dimension Type 2? If time is not available, would you please recommend some learning resources? I haven seen a lot of videos focusing on the fundamental DW concepts, but none like you do to actually provide practical coding examples to the audience. Best Wishes!

[granand]

Thank you Sir. This is what I was looking for.

[JimRohn-uc]

Can I use the Upsert statement if placed in a Transaction to implement Primary and Foreign key constraints along with ON UPDATE CASCADE and ON DELETE CASCADE?

In MPP databases (Snowflake, Synapse, Redshift, etc.) they do NOT enforce constraints except for NOT NULL, so I need to find a way to enforce the aforementioned constraints with SQL/Procedural SQL.

In MPP Databases they only have primary & foreign keys as documentation they aren’t actually enforced.

Btw I love all your videos please don’t ever stop teaching!

[stu]

Hi Brian, Thank you for producing such a great array of videos.
In regards to the video above, Is there a way in which I can use the code demonstrated above to include a type 2 SCD model? I would like to be able to keep all changes to a record over time. For example Salary increases and alike. Best Wishes, Stuart

[lehvzuz]

Hi Brian
I've followed all of your Python and Master SQL with python lessons so far - truly a source of GOLD.
I'm stuck though on this lesson 12 when trying to use conn.execute - at the first hurdle:
conn.execute('DROP TABLE IF EXISTS youtube_customer')
No matter what I try, I always get AttributeError: 'Engine' object has no attribute 'execute'.
Have you come across this error - assuming it is that sqlalchemy has moved on, and doesn't except execute now, but I'm stuck on what the alternative is.
Any help - supremely thankful

[rajsankar]

Bryan, Is there way to merge the data from "databricks- dataframe" to oracle over jdbc connection?

[granand]

I will soon start to extract, create objects in SQL using PL SQL, oracle system. I may get access to Dbeaver editor but please I use python & jupyter

[kosprov]

Is there an easier way to update all the columns together instead of having to pass their names one by one as column_name = excluded.{column_name}.
Thanks for the great vid