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

[@lastdance903]

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!

[@BryanCafferky]

Thank you. And for the new video idea.

[@camilotorres9835]

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

[@BryanCafferky]

Great!

[@JimRohn-u8c]

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!

[@granand]

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

[@rajsankar9867]

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

[@BryanCafferky]

Here's the docs on using JDBC. Does not seem to support Merge. docs.microsoft.com/en-us/azure/databricks/data/data-sources/sql-databases However, if you define a Spark SQL table on top of the Oracle table using JDBC, you may be able to do something like that. Not sure. Delta tables support Merge so maybe this would trick it into letting you do that. I did a video on this awhile back: kzbin.info/www/bejne/kJLInIOweNSMoNU This blog uses a different approach. medium.com/@thomaspt748/how-to-upsert-data-into-relational-database-using-spark-7d2d92e05bb9 A bit of work but looks possible.

[@rajsankar9867]

@@BryanCafferky Thank you for your noble service.

[@stu8924]

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

[@BryanCafferky]

The code demonstrated shows how to execute SQL data maintenance statements so yes, you can use it as a shell to do the more complex SCD Type 2 data maintenance. However, Salary is normally a Fact, not a Dimension, so you may need to re-think how you intend to do reporting on it. If you need to implement an SCD Type 2 dimension, this stackoverflow blog is a place to start. stackoverflow.com/questions/56623502/designing-a-slowly-changing-dimension-type-2-script-with-postgresql

[@stu8924]

​@@BryanCafferky Thank you Bryan.

[@AlanThompson-j2f]

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

[@kosprov69]

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

[@BryanCafferky]

Technically no as far as I can determine. If you delete the row and re-insert it, that would work but that's a lot of overhead. See this StackOverflow blog stackoverflow.com/questions/29926765/how-to-update-all-columns-of-a-record-without-having-to-list-every-column

[@kosprov69]

@@BryanCafferky Thanks for the quick reply. Appreciate the effort!

[@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

[@BryanCafferky]

Are you asking for a video that covers this using Oracle?

[@granand]

@@BryanCafferky I follow your videos so take opportunity to say big thanks & opportunity to learn python & SQL. Yes Sir, soon I would be asked to fetch reports, analyse, update oracle databases & I work for govt & won't install anything. I would be keen to do data analysis, tables update using Python. Your help is appreciated either here or via udemy etc.

[@ravitutika1204]

Thank you

[@miguelmonteiro4456]

Ver good..