Locked learning resources

Join us and get access to thousands of tutorials and a community of expert Pythonistas.

Unlock This Lesson

Locked learning resources

This lesson is for members only. Join us and get access to thousands of tutorials and a community of expert Pythonistas.

Unlock This Lesson

MySQL Databases and Python (Summary)

Nataliya Portman
MySQL Databases and Python Nataliya Portman 04:51
Recommended Tutorial Course Slides (.pdf) Sample Code (.zip) Ask a Question

In this video course, you saw how to use MySQL Connector/Python to integrate a MySQL database with your Python application. You also saw some unique features of a MySQL database that differentiate it from other SQL databases.

Along the way, you learned some programming best practices that are worth considering when it comes to establishing a connection, creating tables, and inserting and updating records in a database application. You also developed a sample MySQL database for an online movie rating system and interacted with it directly from your Python application.

In this video course, you learned how to:

  • Connect your Python app with a MySQL database
  • Bring data from a MySQL database into Python for further analysis
  • Execute SQL queries from your Python application
  • Handle exceptions while accessing the database
  • Prevent SQL injection attacks on your application
Download

Course Slides (.pdf)

 5.9 MB
Download

Sample Code (.zip)

 1.8 KB

00:00 In this summary lesson, I will wrap up everything I have covered in this course, highlighting important concepts and providing recommendations for further advancement of your knowledge.

00:12 In this course, you have learned how to set up local databases. You start with the installation of the local MySQL server suitable for your operating system, followed by setting up the Python MySQL connector that authenticates a user and enables interactions with the MySQL system.

00:34 Next, you establish the connection to the desired database by adding the database parameter to the parameter list of MySQLConnect.connect() function, and specifying its value.

00:47 It is also helpful to set up tools like MySQL Workbench. It has a user-friendly interface to perform administrative tasks such as root and other user account management to check on the server status, to perform data import and export operations, and to write and execute SQL queries.

01:10 You have learned how to create a simple relational data model in Python using linked MySQL tables. As a rule of thumb, you first create parent tables using the CREATE TABLE SQL command, defining ID columns as primary keys.

01:28 Then you create a child table specifying foreign keys in an SQL query and referencing the corresponding columns in the parent tables.

01:39 You have also learned how to import data from CSV files to MySQL tables via pathlib and CSV Python libraries. You have learned how to effectively manipulate and query data using MySQL relational data structures within Python applications.

02:01 Specifically, you know how to retrieve records from the MySQL database using simple SQL queries, containing SELECT commands with an asterisk symbol, and Python methods for fetching data such as .fetchall() and .fetchmany().

02:19 You have seen how a relational data model allows extraction of deeper insights from data like top five money-making movies, for example. You have learned how to write more complex SQL queries that involve table join operations.

02:38 MySQL DBMS is not the one and only way for data storage and retrieval.

02:46 With a powerful SQL toolkit like SQLAlchemy, you do not need a database server to enable database functionality of your code. For further exploration of database topics in Python, I strongly recommend learning SQLAlchemy.

03:04 If you are a beginner, SQLAlchemy Unified tutorial is the place to start, as it has a complex architecture with so-called core and object-relational mapping or ORM APIs.

03:22 SQLAlchemy and Python SQLite allow you to store data in SQL databases and flat files that are often human-readable text files. You can further investigate these topics in SQLite and SQLAlchemy in Python: Move your Data Beyond Flat Files course, that is available in both tutorial and video course formats.

03:49 You can also manage and retrieve data from a MySQL database using FastAPI. FastAPI is a modern framework for building web APIs that easily integrate with MySQL, and I strongly recommend that you learn the main concepts of FastAPI and best practices creating web APIs from the Real Python tutorial and video course on Python REST API with FastAPI.

04:19 Having mastered MySQL, SQLAlchemy, and FastAPI, you’ll be fully equipped with powerful tools to develop high performance web applications that effectively share the same data among different users on different computers.

04:39 Never stop growing your mastery.

04:43 Congratulations! You have completed MySQL Databases and Python course.

Become a Member to join the conversation.