You are reading the article How To Create A Pestle Analysis For Marketing Planning updated in December 2023 on the website Kientrucdochoi.com. We hope that the information we have shared is helpful to you. If you find the content interesting and meaningful, please share it with your friends and continue to follow and support us for the latest updates. Suggested January 2024 How To Create A Pestle Analysis For Marketing Planning
Learn how to create a PESTLE analysis to identify threats and opportunities in the macro environment to guide your marketing planningYou may have heard PESTLE mentioned when studying marketing theory or in your workplace. It is a powerful, yet simple, analysis tool for overall business and marketing planning. In this blog we explain what it is, why it is useful, and how you can create your own PESTLE analysis for marketing planning today.
What is a PESTLE analysis?
PESTLE is an acronym that covers 6 key areas in your macro (external) environment. Each letter stands for a different factor, as shown in the graphic below:
Did you know? Sometimes it is referred to as a PEST analysis, or spelt ‘PESTEL’, but the most common acronym is PESTLE.
A PESTLE analysis is a framework to help you identify external factors – happening outside of your control – that can affect and influence your business objectives and marketing strategy.
Is it necessary to look at external factors when marketing planning?You may initially think that perhaps a PESTLE analysis for marketing planning is going overboard. You have a good marketing plan in place for your business which you are in control of. What is the point of looking at external factors if you cannot change them?
As busy marketers, it is understandable that often we spend more time focussing on our internal environment. But the reality is, without assessing your external environment, you are missing out on identifying opportunities and threats facing your business, whether you acknowledge them or not.
Consider the popular myth about ostriches. They were once thought to stick their head in the sand to hide from predators. To them, they felt nice and safe in their dark hole, when really the rest of their body was very much on show and vulnerable to danger. Not assessing your macro environment is a business’ way of sticking its head in the sand and hoping for the best. It is also a really good way of getting overtaken by your competitors (fellow prey), who are always scanning for potential danger. Not ideal.
Why use a PESTLE analysis for marketing planning?When creating a marketing strategy, you need to assess internal (micro) and external (macro) factors to find opportunities and threats for your plan to tackle. A PESTLE analysis for marketing planning is a great, quick way to see a top-level view of your macro environment. What is going on outside of your internal environment, that you have little/no control over, that will have an impact on your business? It’s a chance to react to external influences and create tactics to tackle any challenges/opportunities.
Top Tip: PESTLE is part of situation analysis which is part of the opportunity part of RACE OSA Planning.
It is common for businesses to focus on the more immediate micro environment factors like customers, competitors and intermediaries to reach and trade with them. While this is important, PESTLE is useful for revealing broader changes in the market which may give opportunities that impact on the micro environment factors and helps review legal factors.
Examples of opportunities and challenges that PESTLE can reveal include:
Societal and cultural changes which impact the go to market strategy
New marketing technology and related communications techniques
Government changes which may give new sources of funding, e.g., R&D, digital apprenticeships
Legal changes that will need to be adhered to (for example, privacy law)
“Why is PESTLE analysis important? Because these factors can influence your business strategy and impact future decisions such as expansion, recruitment, and product development.” (SimplyBusiness).
How do I create a PESTLE analysis for marketing planning?
To create your PESTLE analysis, you need to research your macro environment. You are looking for Political, Economic, Social, Technological, Legal and Environmental events/changes that will impact your business and future goals. Have a look at respectable news and industry forums and websites to start.
Then you need to assess what that impact is. Is it High/Medium/Low? What sort of time-scale is the impact? Is it an Opportunity or Threat for your business? Or both?
Top Tip: Not all 6 PESTLE elements may be relevant for your business; focus on areas that have a definite impact.
Presenting your PESTLE analysisThe easiest way to present your PESTLE analysis is in a table format. Smart Insights have created an easy-to-use PESTLE template to help you create and display your analysis.
PESTLE analysis template
Identify threats and opportunities in the macro environment to feed your marketing strategy with our actionable template.
Access the PESTLE analysis template
The table splits out the key information you need to assess for each PESTLE factor.
O/T – This stands for Opportunity or Threat – is the impact on your business positive or negative?
Importance – Is it high, medium or low for your business? This is a good column for prioritizing action.
Time impact – When will this affect your business?
Implications for business goals – Most useful column to complete. This is where you bring all the information together to create actionable insight. What is the main implication of this external factor on your business specifically? How can you respond to it?
B2C PESTLE example
In our PESTLE analysis template we created a completed example for a fictitious B2C company to help you conduct your own analysis.
Let’s take a look at an example of a factor that could be an opportunity and a threat.
Here, our customers are becoming aware of the impact of their consumerism on the environment. If we do nothing about this, our company will lose business as we are not adapting to our customers’ needs. However, we now know what our customers want. This creates an opportunity for us to meet that need, to retain and gain more customers by improving our business’ environmental impact and using that as a point of difference in marketing comms.
Download our PESTLE analysis template to see a complete example across all external factors.
How often do I need to conduct a PESTLE analysis?As marketers, we know that our digital environment changes constantly. It is why we are masters of optimization! The same can be said for our external environment. Changes to our macro environment happen often and swiftly, so it is recommended to conduct a PESTLE analysis regularly. Oxford College of Marketing suggests conducting a PESTLE analysis every six months:
If this is too unrealistic for your business, we recommend creating a new PESTLE analysis every 12 months.
Turning your PESTLE analysis into actionYour PESTLE analysis is a great basis for identifying potential threats and opportunities that you can use in a SWOT/TOWS analysis. The TOWs matrix brings together strategic opportunities and threats to tackle in your strategy.
We have a dedicated template to help you conduct your SWOT analysis. It also goes through how you can use RACE to prioritize your key issues and create actions.
We hope you’ve found this blog helpful. Download our PESTLE analysis template for more tips on how to make the most of your analysis, and to see a completed worked example with a video explainer.
PESTLE analysis template
Identify threats and opportunities in the macro environment to feed your marketing strategy with our actionable template.
Access the PESTLE analysis template
You're reading How To Create A Pestle Analysis For Marketing Planning
Powerpoint Digital Marketing Strategy Planning Template
PowerPoint digital marketing strategy planning template Create a detailed digital marketing strategy structured using our RACE planning system to present to senior management or clients How will this template help me and my business?
If you’re working on your first digital strategy or integrated multichannel marketing review, it can be difficult to know if you’re using the best structure or using the right tools to assess your capabilities and strategic options. You need a strategy summary document that clearly lays out what you need to achieve and how you’re going to achieve it. That’s why we’ve created this strategy planning template – one of our most popular resources – split into two useful documents.
In your download, you get:
1.) Guidance and examples document – this document provides an overview on creating your strategy, with a range of completed examples to give you an idea of how to complete certain templates.
2.) Blank template for you to complete – the accompanying resource in this download is a blank presentation deck that you can use to create your own digital marketing strategy presentation, using your company’s analysis, KPIs, channels etc.
Whether you need to create a brand new strategy or review specific digital marketing techniques such as content, search or social media strategy for an upcoming project, this template will help you summarize your strategy. Digital veterans can place their initiatives into this template to present them to others, whereas members who need more guidance can follow the instructions in the notes section of each slide to complete their strategy.
Who is it for?This slide deck is for anyone who is charged with developing an integrated digital marketing strategy and achieving buy-in to the plan from colleagues or clients, including:
Digital marketing managers and marketing managers
CMOs and marketing directors
Account managers and consultants working at agencies to develop strategies for their clients
Our members who are consultants or work for agencies can use it to present their analysis and recommended strategies to their clients. Others, who work for brands, can use it to explain and justify strategies to their colleagues in the business.
You can use the analysis in the comprehensive blank templates to review every digital marketing tactic to assess your own capabilities and define your future roadmap. Guidance and examples are provided so you can see completed versions of the templates.
The examples cover business-to-consumer (B2C) and business-to-business (B2B) examples, plus our library also contains examples from specific sectors such as consumer brands, financial services, healthcare/pharma, retail e-commerce and travel.
How is the template structured?We have structured this template around the 25 elements of the Smart Insights RACE marketing planning system so that you can clearly define a comprehensive digital strategy that fits into each step of the framework:
Plan: Ensuring you’re able to plan for success by knowing how well you’re performing, what your internal capabilities are, and how you stack up against competitors.
Reach: Your strategy needs to show how you will invest to reach new customers through search marketing, owned, earned and paid media.
Act: Once you’ve attracted visitors to your website, you need to persuade them to act by improving customer journeys and optimizing your content and landing pages.
Convert: Depending on your type of business, conversion may happen online or offline. This part of the strategy covers lead nurturing, personalization and multichannel selling activities to make this happen.
Each element of RACE is broken down into five tasks with summary tables so you can present recommendations for all aspects of your digital marketing.
The template is presented as two files:
One ‘README’ guidance and examples document that instructs you through creating your strategy and provides you with completed examples.
One ‘BLANK’ file for you to use when creating your own strategy document.
The notes tab of each slide contains explanations, summaries, and instructions based on marketing tasks from our Microsoft Word-based Digital marketing plan workbook to help you through every element of your strategy.
Latest updatesWe’ve improved the guidance and examples document so it contains more useful information about how to create a strategy using RACE, with updated completed examples to help you complete the templates. We’ve also added a new visual scorecard option that is better for presenting and added 6 pillars for assessing digital marketing capabilities for smaller businesses.
The blank template doc has been re-designed giving you more space to complete the templates and contains new useful models (including our new customer journey mapping template) and links to other supporting resources. We have cross-referenced where there is a completed example in the READ ME guide to help you complete the blank template.
Resource Details
Author: Dr. Dave Chaffey
Editor: Amelia Mayes
Format: PowerPoint files
About the authorDr. Dave Chaffey
Dave is co-founder of Smart Insights and creator of the Smart Insights RACE planning framework. For his full profile, or to connect on LinkedIn or other social networks, see the About Dave Chaffey profile page on Smart Insights. Dave is author of 5 bestselling books on digital marketing including Digital Marketing Excellence and Digital Marketing: Strategy, Implementation and Practice. In 2004 he was recognized by the Chartered Institute of Marketing as one of 50 marketing ‘gurus’ worldwide who have helped shape the future of marketing.
How To Create A Daemonset?
Definition of Kubernetes Daemonset
Web development, programming languages, Software testing & others
What is Kubernetes Daemonset?A Daemonset is a dynamic object in kubernetes which is managed by a controller. The user can set the desired state that represents the specific pods which need to exist on every node. The compromise in the control loop can compare the current practical state with the desired state. If the practical node, doesn’t fit the matching pod, then the controller of the Daemonset creates a new one automatically. This automated process has all recently created nodes and existing nodes. The pods developed by the controller of Daemonset are neglected by the Kubernetes scheduler and present as the same node itself.
What are the Use Case for Daemonset?The use case for Daemonset are as follows,
As the use case is complex, the user can deploy many possible Daemonsets for a typical kind by a variety of flags, CPU requests, and memory units for different types of hardware.
Daemonset can enhance the performance of the cluster by pod deployment to execute maintenance activities and support services for every node. The background processes like monitoring apps in Kubernetes and other services must be present along with clusters to offer timely and relevant services.
It is mostly adapted for long-running services like collection of logs, monitoring of node resources, storage in clusters, and pods related to infrastructure. It is standard for Daemonset to run on a single daemon throughout all the nodes in the cluster. The multiple Daemonset can manage one type of Daemonset by applying different labels. This label can identify rules in deployment based on the behavior of individual nodes. Daemonsets is deployed on the background tasks which the user can execute on all the possible nodes but it doesn’t need any intervention. The user can have Daemonset for every type of daemon to execute on all nodes. The user can also execute multiple Daemonset for every single daemon type. But can use various configurations for diverse hardware models and resource requirements.
How to Create a Daemonset?
To create a Daemonset the following steps are involved.
The Daemonset is developed in a YAML file with few parts.
It requires apiVersion
It requires the type or kind of the behavior for Daemonset
It needs the metadata for the Daemonset
It needs spec_template for pod definition which the user needs to execute on all nodes.
It needs spec_selector to manage the pods for Daemonset and this kind must be label specific in a template of pods. The selector name defined in the template is applied in the selector. But this name cannot be changed once the Daemonset is created without leaving the pods created prior in the Daemonset.
The spec_template-spec-node selector is used to execute only the nodes subset which suits the selector
Spec-template-spec-affinity is used to execute on nodes subset which has affinity match.
Once the configuration is completed, Daemonset is created in the cluster.
Methods to communicate with pods Kubernetes DaemonsetThe user can transfer data from other pods to a pod of Daemonset executed on the node. The user can transmit data metrics to pod monitoring in two ways,
The pod spec in Daemonset has specifiy host port to uncover it on the node. Then it can interact directly with the IP of node which is executed on it.
Using the same pod selector, the service is created as a Daemonset and then it is used to reach the Daemonset. The limitation of this service is that a Daemonset of a pod is random to receive the request. This implies that the request may pass over the network instead of hanging on a similar pod as the requested pod.
In NodeIP with Known port, the Daemonset pods use the hostport, and so the pods are connected through node IPs. The client understands the use nodeIP list and is also aware of the port conventions.
DNS develop headless service and explore Daemonset using endpoint to retrieve reports from DNS
In-Service, it creates the service with a similar pod selector and uses it to reach the daemon of the random node.
ConclusionHence, the feature of Daemonset is used to check that few or all the pods are executed and scheduled on every available node. It executes the copy of the desired pod throughout all the nodes. So when a new node is joined to the cluster of Kubernetes, the new pod is added to the recently attached node.
Recommended ArticlesThis is a guide to Kubernetes Daemonset. Here we discuss Definition, What is Kubernetes Daemonset? How to create a Daemonset?. You may also have a look at the following articles to learn more –
How To Create A Button Programmatically?
In this article, you will learn about how to create a button prograticamtilly in the Swift language. There are different ways to create a button object programmatically in iOS. Let’s explore some of them with an example.
We will see the following ways to create a button −
Step 1 − In this step, we will create a button object and customize it later when required.
Step 2 − In this step, we will create a button object using a lazy keyword.
Step 3 − In this step, we will create a button object and set the frame later.
Create a button and customize it laterHere is an example of how you can create a button programmatically in iOS using Swift −
Example import UIKit class TestController: UIViewController { private let loginButton = UIButton() override func viewDidLoad() { super.viewDidLoad() initialSetup() } private func initialSetup() { view.backgroundColor = .white navigationItem.title = "UIButton" loginButton.setTitle("Login", for: .normal) loginButton.setTitleColor(.white, for: .normal) loginButton.layer.cornerRadius = 10 loginButton.layer.masksToBounds = true loginButton.backgroundColor = .darkGray loginButton.titleLabel?.font = UIFont.systemFont(ofSize: 18, weight: .semibold) loginButton.addTarget(self, action: #selector(handleLoginButtonTapped), for: .touchUpInside) view.addSubview(loginButton) loginButton.translatesAutoresizingMaskIntoConstraints = false loginButton.centerYAnchor.constraint(equalTo: view.centerYAnchor).isActive = true loginButton.centerXAnchor.constraint(equalTo: view.centerXAnchor).isActive = true loginButton.heightAnchor.constraint(equalToConstant: 50).isActive = true loginButton.widthAnchor.constraint(equalToConstant: 280).isActive = true } @objc private func handleLoginButtonTapped() { print("login button tapped...") } } OutputIn the above step, a button object named loginButton is created in the controller. The button’s title is set to “Login” and the title color is set to white. The button is also given a target-action pair so that when the button is tapped, the function “handleLoginButtonTapped” will be called. Finally, the button is added as a subview to the current view.
You will see that initially, we created a button object. After that, we will customize the button in the viewDidLoad() method. And finally, add it to the super view with some required constraints.
Create a button using the lazy keywordHere is an example of how you can create a button programmatically in iOS using the lazy keyword in UIKit −
Example import UIKit class TestController: UIViewController { private lazy var loginButton: UIButton = { let button = UIButton() button.setTitle("Login", for: .normal) button.setTitleColor(.white, for: .normal) button.layer.cornerRadius = 10 button.layer.masksToBounds = true button.backgroundColor = .darkGray button.titleLabel?.font = UIFont.systemFont(ofSize: 18, weight: .semibold) button.addTarget(self, action: #selector(handleLoginButtonTapped), for: .touchUpInside) button.translatesAutoresizingMaskIntoConstraints = false return button }() override func viewDidLoad() { super.viewDidLoad() initialSetup() } private func initialSetup() { view.backgroundColor = .white navigationItem.title = "UIButton" view.addSubview(loginButton) loginButton.centerYAnchor.constraint(equalTo: view.centerYAnchor).isActive = true loginButton.centerXAnchor.constraint(equalTo: view.centerXAnchor).isActive = true loginButton.heightAnchor.constraint(equalToConstant: 50).isActive = true loginButton.widthAnchor.constraint(equalToConstant: 280).isActive = true } @objc private func handleLoginButtonTapped() { print("login button tapped...") } } OutputIn the above step, a button object named loginButton is created in the controller using the lazy keyword. The button’s title is set to “Login” and the title color is set to white. The button is also given a target-action pair so that when the button is tapped, the function “handleLoginButtonTapped” will be called. Finally, the button is added as a subview to the current view.
Note that with lazy keyword you don’t need to add the button as subview in the declaration of button, you can do it later whenever you need it.
Create a button using a frameHere is an example of how you can create a button programmatically in iOS using Swift −
Example import UIKit class TestController: UIViewController { private let loginButton = UIButton(type: .system) override func viewDidLoad() { super.viewDidLoad() initialSetup() } private func initialSetup() { view.backgroundColor = .white navigationItem.title = "UIButton" loginButton.frame = CGRect(x: 20, y: 150, width: 280, height: 50) loginButton.setTitle("Login", for: .normal) loginButton.setTitleColor(.white, for: .normal) loginButton.layer.cornerRadius = 10 loginButton.layer.masksToBounds = true loginButton.backgroundColor = .darkGray loginButton.titleLabel?.font = UIFont.systemFont(ofSize: 18, weight: .semibold) loginButton.addTarget(self, action: #selector(handleLoginButtonTapped), for: .touchUpInside) view.addSubview(loginButton) } @objc private func handleLoginButtonTapped() { print("login button tapped...") } } OutputYou can change x, y, width and height according to your needs and also you can change the button title and action according to your requirement.
ConclusionYou can programmatically create buttons in a variety of ways. You can create a button object and customize it later whenever you need it in code. In addition, the lazy keyword can be used to create a button object. The UIButton class provides the flexibility to set the frame later in the code. You can see how to set the frame of a button in the above example.
Using a lazy keyword is the most recommended way. You can create a button object and add any customization in a single block. Also, you can add the target in the same block.
Python And Mysql: A Practical Introduction For Data Analysis
This article was published as a part of the Data Science Blogathon
IntroductionLet’s look at a practical example of how to make SQL queries to a MySQL server from Python code: CREATE, SELECT, UPDATE, JOIN, etc.
Most applications interact with data in some form. Therefore, programming languages (Python is no exception) provide tools for storing data sources and accessing them. MySQL is one of the most fantastic and rich database management systems ( DBMS ). Last year it was ranked second after Oracle in the database rankings.
Using the techniques described in this tutorial, you can effectively integrate a MySQL database into your Python application. In this tutorial, we will develop a small MySQL database for a movie rating system and learn how to grab data from it using Python code.
What you will get to know after this tutorial is:
Connect your application to the MySQL database
Retrieval of data via a query for the required data from the database
Handle exceptions thrown when accessing the database
Comparing MySQL to Another SQL DatabasesSQL stands for Structured Query Language is a widely-used programming language for managing relational databases. You may have heard of various SQL-based DBMS: MySQL, PostgreSQL, SQLite, and SQL Server. All of these databases comply with SQL standards but differ in detail.
Because of its open-source code, MySQL quickly became the market leader in SQL solutions. MySQL is currently used by most of the famous tech firms like Google, LinkedIn, Uber, Netflix, Twitter, and more.
Besides the support from the open-source community, there are other reasons for MySQL’s success:
Easy to install- MySQL is designed to be user-friendly. The database is easy to create and customize. MySQL is available for major operating systems including Windows, macOS, Linux, and Solaris.
Speed- MySQL has a reputation for being a fast database solution. This DBMS also scales well.
User rights and security- MySQL allows you to set password security levels, add and remove privileges to user accounts. User rights management looks much simpler than in many other DBMS such as PostgreSQL, where managing configuration files requires some skill.
Installing MySQL Server and MySQL ConnectorMySQL Server and MySQL Connector are the only two software that you need to get started with this tutorial. MySQL Server will provide the resources needed to work with the database. After starting the server, you should be able to connect your Python application to it using the MySQL Connector / Python.
Installing MySQL Server
The official documentation describes the recommended ways to download and install MySQL Server. There are instructions for all popular operating systems, including Windows, macOS, Solaris, Linux, and many more.
For Windows, your best bet is to download the MySQL installer and let it take care of the process. The Installation Manager will also help you configure the security settings for your MySQL server. On the accounts page, you will need to enter a password for the root account and, if desired, add other users with different privileges.
Setting up a MySQL account
Other useful tools such as MySQL Workbench can be customized using the installers. A convenient alternative to installing on an operating system is to deploy MySQL using Docker.
Installing MySQL Connector / Python
Database driver – software that allows an application to connect to and interact with a DBMS. These drivers are usually supplied as separate modules. The standard interface that all Python database drivers must conform to is described in PEP 249. To install the driver (connector), we will use the package manager: pip
pip install mysql-connector-python
pip will install the connector into the currently active environment. To work with a project in isolation, we recommend setting up a virtual environment.
Let’s check the installation result by running the following command in the Python terminal:
import mysql.connector
If the import statement runs without errors, then it is successfully installed and ready to use. MySQL.connector
Establishing a connection to the MySQL serverMySQL is a server-side database management system. One server can contain multiple databases. To interact with the database, we must establish a connection to the server. Step by step interaction for a Python program with a MySQL-based database looks like this:
We connect to the MySQL server.
We create a new database (if necessary).
We connect to the database.
We execute the SQL query, collect the results.
We inform the database if changes have been made to the table.
Lastly, just close the connection to the MySQL server.
Whatever the application, the first step is to link the application and database together.
Connecting to MySQL Server from PythonTo establish a connection, use the module. This function takes parameters, and, and returns an object. Credentials can be obtained as a result of input from the user: connect() mysql.connector host user password MySQLConnection
from getpass import getpass from mysql.connector import connect, Error try: with connect( host="localhost", user = input ("Username:"), password = getpass ("Password:"), ) as connection: print(connection) except Error as e: print(e)The object is stored in a variable that we will use to access the MySQL server. A few important points: MySQLConnection connection
Wrap all database connections in blocks. This will make it easier to catch and examine any exceptions. try … except
Remember to close the connection after you finish accessing the database. Unused open connections lead to unexpected errors and performance problems. The code uses the context manager ( with … as …) for this.
You should never embed credentials (username and password) in string form in a Python script. This is bad deployment practice and poses a serious security risk. The code above asks for your login credentials. For this, a built-in module is used to hide the entered password.
So, we have established a connection between our program and the MySQL server. Now you need to either create a new database or connect to an existing one.
Create a new databaseTo create a new database, for example with a name, you need to execute the SQL statement: online_movie_rating
CREATE DATABASE online_movie_rating;
Note
MySQL requires you to put a semicolon ( 😉 at the end of a statement. However, MySQL Connector/Python automatically adds a semicolon at the end of each query.
To execute an SQL query, we need a cursor that abstracts the process of accessing database records. MySQL Connector / Python provides a corresponding class, an instance of which is also called a cursor. MySQLCursor
Let’s pass our request to create a database: online_movie_rating
try: with connect( host="localhost", user = input ("Username:"), password = getpass ("Password:"), ) as connection: create_db_query = "CREATE DATABASE online_movie_rating" with connection.cursor() as cursor: cursor.execute(create_db_query) except Error as e: print(e)The request is stored as a string in a variable and then passed for execution to CREATE DATABASE create_db_query cursor.execute()
If a database with the same name already exists on the server, we will receive an error message. Using the same object as before, let’s run a query to see all the tables stored in the database: MySQLConnection SHOW DATABASES
try: with connect( host="localhost", user = input ("Username:"), password = getpass ("Password:"), ) as connection: show_db_query = "SHOW DATABASES" with connection.cursor() as cursor: cursor.execute(show_db_query) for db in cursor: print(db) except Error as e: print(e)OUTPUT
Enter username: root
Enter password: ········
(‘information_schema’,)
(‘mysql’,)
(‘online_movie_rating’,)
(‘performance_schema’,)
(‘sys’,)
The above code will print the names of all databases located on our MySQL server. The command in our example also dumped databases that are automatically created by the MySQL server and provide access to database metadata and server settings. SHOW DATABASES
Connecting to an existing databaseSo, we have created a database called. To connect to it, we simply supplement the call with a parameter: online_movie_rating connect() database
try: with connect( host="localhost", user = input ("Username:"), password = getpass ("Password:"), database="online_movie_rating", ) as connection: print(connection) except Error as e: print(e) Creating, modifying, and dropping tablesIn this section, we discuss how to use Python to perform some basic queries: ’,’ and ‘.’ CREATE TABLE DROP ALTER
Defining the database schema
Let’s start by creating a database schema for the movie rating system. Take the database comprised of three tables:
1. movies- general information about films:
Id
+
title
release year
genre
collection_in_mi
2. reviewers- information about the people who published the ratings of the films:
id
first_name
last_name
3. ratings- information about the ratings of films by reviewers:
movie_id (foreign key)
reviewer_id (foreign key)
rating
These three tables are sufficient for the purposes of this guide.
Film rating system diagram
The tables in the database are related to each other: movies and reviewers must have a many-to-many relationship: one movie can be viewed by multiple reviewers, and one reviewer can review multiple movies. The table ratings connect the movies table to the reviewer’s table.
Creating tables using the CREATE TABLE statement
To create a new table in MySQL, we need to use the operator. The following MySQL query will create our database table: CREATE TABLE movies online_movie_rating
CREATE TABLE movies( id INT AUTO_INCREMENT PRIMARY KEY, title VARCHAR(100), release_year YEAR(4), genre VARCHAR(100), collection_in_mil INT );If you have encountered SQL before, you will understand the meaning of the above query. The MySQL dialect has some distinctive features. For example, MySQL offers a wide range of data types, including, and so on. In addition, MySQL uses the keyword when the column value should be automatically incremented when new records are inserted. YEAR INT BIGINT AUTO_INCREMENT
To create a table, you need to pass the specified query to the cursor.execute()
create_movies_table_query = """ CREATE TABLE movies( id INT AUTO_INCREMENT PRIMARY KEY, title VARCHAR(100), release_year YEAR(4), genre VARCHAR(100), collection_in_mil INT ) """ with connection.cursor() as cursor: cursor.execute(create_movies_table_query)Let’s repeat the procedure for the table: reviewers
create_reviewers_table_query = “””
CREATE TABLE reviewers ( id INT AUTO_INCREMENT PRIMARY KEY, first_name VARCHAR(100), last_name VARCHAR(100) ) """ with connection.cursor() as cursor: cursor.execute(create_reviewers_table_query) Finally, let's create a table ratings: create_ratings_table_query = """ CREATE TABLE ratings ( movie_id INT, reviewer_id INT, rating DECIMAL(2,1), FOREIGN KEY(movie_id) REFERENCES movies(id), FOREIGN KEY(reviewer_id) REFERENCES reviewers(id), PRIMARY KEY(movie_id, reviewer_id) ) """ with connection.cursor() as cursor: cursor.execute(create_ratings_table_query)The implementation of foreign key relationships in MySQL is slightly different and has limitations compared to standard SQL. In MySQL, both parent and child of a foreign key must use the same storage engine — the underlying software component that the database management system uses to perform SQL operations. MySQL offers two kinds of such mechanisms:
Transactional storage engines are transaction-safe and allow you to roll back transactions using simple commands such as. Many popular MySQL engines fall into this category, including InnoDB and NDB. rollback
Non-transactional storage engines rely on manual code to undo statements committed to the database. These are, for example, MyISAM and MEMORY.
InnoDB is the most popular default storage engine. By enforcing foreign key constraints, it helps maintain data integrity. This means that any foreign key CRUD operation is pre-checked to ensure that it does not lead to inconsistency between different tables.
Note that the table uses columns and two foreign keys, acting together as a primary key. This feature ensures that a reviewer cannot rate the same film twice. ratings movie_id reviewer_id
The same cursor can be used for multiple hits. In this case, all calls will become one atomic transaction. For example, you can execute all statements with one cursor, and then commit the transaction at once:
CREATE TABLE with connection.cursor() as cursor: cursor.execute(create_movies_table_query) cursor.execute(create_reviewers_table_query) cursor.execute(create_ratings_table_query)Displaying Table Schema Using the DESCRIBE Statement
We have created three tables and can view the schema using the operator. DESCRIBE
Assuming you already have an object in a variable, we can print the results obtained. This method retrieves all lines from the last executed statement: MySQLConnection connection cursor.fetchall()
show_table_query = "DESCRIBE movies" with connection.cursor() as cursor: cursor.execute(show_table_query) # Fetch rows from last executed query result = cursor.fetchall() for row in result: print(row)OUTPUT
(‘id’, ‘int(11)’, ‘NO’, ‘PRI’, None, ‘auto_increment’)
(‘title’, ‘varchar(100)’, ‘YES’, ”, None, ”)
(‘release_year’, ‘year(4)’, ‘YES’, ”, None, ”)
(‘genre’, ‘varchar(100)’, ‘YES’, ”, None, ”)
(‘collection_in_mil’, ‘int(11)’, ‘YES’, ”, None, ”)
After executing the above code, we should get a table containing information about the columns in the table. For each column, information is displayed about the data type, whether the column is a primary key, and so on. movies
Changing the schema of a table using the ALTER statement
The name column in the table contains the movie’s box office in millions of dollars. We can write the following MySQL statement to change the data type of an attribute from to collection_in_mil movies collection_in_mil INT DECIMAL
ALTER TABLE movies MODIFY COLUMN collection_in_mil DECIMAL(4,1);
DECIMAL(4,1) indicates a decimal number, which can have a maximum of four figures, of which one corresponds to the tenth discharge, for example, and so on. d. 120.1 3.4 38.0
alter_table_query = """ ALTER TABLE movies MODIFY COLUMN collection_in_mil DECIMAL(4,1) """ show_table_query = "DESCRIBE movies" with connection.cursor() as cursor: cursor.execute(alter_table_query) cursor.execute (show_table_query) # Get rows from the last executed query result = cursor.fetchall () print ("Movie table schema after modification:") for row in result: print(row) The movie table schema after making changes: ('id', 'int(11)', 'NO', 'PRI', None, 'auto_increment') ('title', 'varchar(100)', 'YES', '', None, '') ('release_year', 'year(4)', 'YES', '', None, '') ('genre', 'varchar(100)', 'YES', '', None, '') ('collection_in_mil', 'decimal(4,1)', 'YES', '', None, '')As shown in the output, the attribute changed its type too. Note that in the above code, we are calling twice, but only fetches rows from the most recently executed query, which is. collection_in_mil DECIMAL(4,1) cursor.execute() cursor.fetchall() show_table_query
Dropping tables using the DROP statement
To delete tables, use the operator. Dropping a table is an irreversible process. If you run the code below, you will need to invoke the query on the table again: DROP TABLE CREATE TABLE ratings
drop_table_query = "DROP TABLE ratings" with connection.cursor() as cursor: cursor.execute(drop_table_query) Inserting records into tablesLet’s fill the tables with data. In this section, we will look at two ways to insert records using the MySQL Connector in Python code.
The first method works well when the number of records is small. The second one is better suited for real-life scenarios. .execute() .executemany()
Inserting records with .execute ()
The first approach uses the same method that we have been using so far. We write a request and send it to the cursor.execute() INSERT INTO cursor.execute()
insert_movies_query = """ INSERT INTO movies (title, release_year, genre, collection_in_mil) VALUES ("Forrest Gump", 1994, "Drama", 330.2), ("3 Idiots", 2009, "Drama", 2.4), ("Eternal Sunshine of the Spotless Mind", 2004, "Drama", 34.5), ("Good Will Hunting", 1997, "Drama", 138.1), ("Skyfall", 2012, "Action", 304.6), ("Gladiator", 2000, "Action", 188.7), ("Black", 2005, "Drama", 3.0), ("Titanic", 1997, "Romance", 659.2), ("The Shawshank Redemption", 1994, "Drama",28.4), ("Udaan", 2010, "Drama", 1.5), ("Home Alone", 1990, "Comedy", 286.9), ("Casablanca", 1942, "Romance", 1.0), ("Avengers: Endgame", 2023, "Action", 858.8), ("Night of the Living Dead", 1968, "Horror", 2.5), ("The Godfather", 1972, "Crime", 135.6), ("Haider", 2014, "Action", 4.2), ("Inception", 2010, "Adventure", 293.7), ("Evil", 2003, "Horror", 1.3), ("Toy Story 4", 2023, "Animation", 434.9), ("Air Force One", 1997, "Drama", 138.1), ("The Dark Knight", 2008, "Action",535.4), ("Bhaag Milkha Bhaag", 2013, "Sport", 4.1), ("The Lion King", 1994, "Animation", 423.6), ("Pulp Fiction", 1994, "Crime", 108.8), ("Kai Po Che", 2013, "Sport", 6.0), ("Beasts of No Nation", 2023, "War", 1.4), ("Andadhun", 2023, "Thriller", 2.9), ("The Silence of the Lambs", 1991, "Crime", 68.2), ("Deadpool", 2023, "Action", 363.6), ("Drishyam", 2023, "Mystery", 3.0) """ with connection.cursor() as cursor: cursor.execute(insert_movies_query)Inserting records with .executemany ()
The previous approach is well suited for the smaller record that can be inserted easily via code. But usually, the data is stored in a file or generated by another script. Here’s where it comes in handy. The method takes two parameters: .executemany()
A query containing placeholders for the records to be inserted.
List of records to insert.
Let’s take an approach to fill the table: reviewers
insert_reviewers_query = """ INSERT INTO reviewers (first_name, last_name) VALUES ( %s, %s ) """ reviewers_records = [ ("Chaitanya", "Baweja"), ("Mary", "Cooper"), ("John", "Wayne"), ("Thomas", "Stoneman"), ("Penny", "Hofstadter"), ("Mitchell", "Marsh"), ("Wyatt", "Skaggs"), ("Andre", "Veiga"), ("Sheldon", "Cooper"), ("Kimbra", "Masters"), ("Kat", "Dennings"), ("Bruce", "Wayne"), ("Domingo", "Cortes"), ("Rajesh", "Koothrappali"), ("Ben", "Glocker"), ("Mahinder", "Dhoni"), ("Akbar", "Khan"), ("Howard", "Wolowitz"), ("Pinkie", "Petit"), ("Gurkaran", "Singh"), ("Amy", "Farah Fowler"), ("Marlon", "Crafford"), ] with connection.cursor() as cursor: cursor.executemany(insert_reviewers_query, reviewers_records)This code takes placeholders for two strings that are inserted into. Placeholders act as format specifiers and help to reserve space for a variable within a string. %s insert_reviewers_query
Let’s fill in the table in the same way: ratings
insert_ratings_query = """ INSERT INTO ratings (rating, movie_id, reviewer_id) VALUES ( %s, %s, %s) """ ratings_records = [ (6.4, 17, 5), (5.6, 19, 1), (6.3, 22, 14), (5.1, 21, 17), (5.0, 5, 5), (6.5, 21, 5), (8.5, 30, 13), (9.7, 6, 4), (8.5, 24, 12), (9.9, 14, 9), (8.7, 26, 14), (9.9, 6, 10), (5.1, 30, 6), (5.4, 18, 16), (6.2, 6, 20), (7.3, 21, 19), (8.1, 17, 18), (5.0, 7, 2), (9.8, 23, 3), (8.0, 22, 9), (8.5, 11, 13), (5.0, 5, 11), (5.7, 8, 2), (7.6, 25, 19), (5.2, 18, 15), (9.7, 13, 3), (5.8, 18, 8), (5.8, 30, 15), (8.4, 21, 18), (6.2, 23, 16), (7.0, 10, 18), (9.5, 30, 20), (8.9, 3, 19), (6.4, 12, 2), (7.8, 12, 22), (9.9, 15, 13), (7.5, 20, 17), (9.0, 25, 6), (8.5, 23, 2), (5.3, 30, 17), (6.4, 5, 10), (8.1, 5, 21), (5.7, 22, 1), (6.3, 28, 4), (9.8, 13, 1) ] with connection.cursor() as cursor: cursor.executemany(insert_ratings_query, ratings_records)All three tables are now filled with data. The next step is to figure out how to interact with this database.
Reading records from the database
So far, we have only created database items. It’s time to run a few queries and find the properties we are interested in. In this section, we will learn how to read records from database tables using the operator. SELECT
Reading records with a SELECT statement
To get records, you need to send to the request and return the result using: cursor.execute() SELECT cursor.fetchall()
select_movies_query = "SELECT * FROM movies LIMIT 5" with connection.cursor() as cursor: cursor.execute(select_movies_query) result = cursor.fetchall() for row in result: print(row)OUTPUT
(1, ‘Forrest Gump’, 1994, ‘Drama’, Decimal(‘330.2’))
(2, ‘3 Idiots’, 2009, ‘Drama’, Decimal(‘2.4’))
(3, ‘Eternal Sunshine of the Spotless Mind’, 2004, ‘Drama’, Decimal(‘34.5’))
(4, ‘Good Will Hunting’, 1997, ‘Drama’, Decimal(‘138.1’))
(5, ‘Skyfall’, 2012, ‘Action’, Decimal(‘304.6’))
The variable contains the records returned. It is a list of tuples representing individual records in a table. result .fetchall()
In the above query, we use a keyword to limit the number of rows received from the operator. Developers are often used to paginate output when processing large amounts of data. LIMIT SELECT LIMIT
In MySQL, two non-negative numeric arguments can be passed to an operator: LIMIT
SELECT * FROM movies LIMIT 2,5;When using two numeric arguments, the first specifies an offset, which in this example is 2, and the second limits the number of rows returned to 5. That is, the query from the example will return rows 3 through 7.
select_movies_query = "SELECT title, release_year FROM movies LIMIT 2, 5" with connection.cursor() as cursor: cursor.execute(select_movies_query) for row in cursor.fetchall(): print(row)OUTPUT
(‘Eternal Sunshine of the Spotless Mind’, 2004)
(‘Good Will Hunting’, 1997)
(‘Skyfall’, 2012)
(‘Gladiator’, 2000)
(‘Black’, 2005)
Filtering Results with WHERETable entries can also be filtered using. To get all films with a box office of over $ 300 million, run the following query: WHERE
select_movies_query = """ SELECT title, collection_in_mil FROM movies ORDER BY collection_in_mil DESC """ with connection.cursor() as cursor: cursor.execute(select_movies_query) for movie in cursor.fetchall(): print(movie) ('Avengers: Endgame', Decimal('858.8')) ('Titanic', Decimal('659.2')) ('The Dark Knight', Decimal('535.4')) ('Toy Story 4', Decimal('434.9')) ('The Lion King', Decimal('423.6')) ('Deadpool', Decimal('363.6')) ('Forrest Gump', Decimal('330.2')) ('Skyfall', Decimal('304.6'))The phrase in the query allows you to sort the fees from highest to lowest. ORDER BY
MySQL provides many string formatting operations such as for string concatenation. For example, movie titles are usually displayed along with the release year to avoid confusion. Let’s get the names of the five most profitable films along with their release dates: CONCAT
select_movies_query = """ SELECT CONCAT(title, " (", release_year, ")"), collection_in_mil FROM movies ORDER BY collection_in_mil DESC LIMIT 5 """ with connection.cursor() as cursor: cursor.execute(select_movies_query) for movie in cursor.fetchall(): print(movie)OUTPUT
(‘Avengers: Endgame (2023)’, Decimal(‘858.8’))
(‘Titanic (1997)’, Decimal(‘659.2’))
(‘The Dark Knight (2008)’, Decimal(‘535.4’))
(‘Toy Story 4 (2023)’, Decimal(‘434.9’))
(‘The Lion King (1994)’, Decimal(‘423.6’))
If you do not want to use and do not need to get all records, you can use the cursor methods and: LIMIT .fetchone() .fetchmany()
.fetchone() Retrieves the next row of the result as a tuple, or if there are no more rows available. None
.fetchmany() Retrieves a list of the next set of rows as a tuple. To do this, an argument is passed to it, which by default is 1. If there are no more rows available, the method returns an empty list.
Again, extract the titles of the five highest-grossing films by year, but this time using: .fetchmany()
select_movies_query = """ SELECT CONCAT(title, " (", release_year, ")"), collection_in_mil FROM movies ORDER BY collection_in_mil DESC """ with connection.cursor() as cursor: cursor.execute(select_movies_query) for movie in cursor.fetchmany(size=5): print(movie) cursor.fetchall()OUTPUT
(‘Avengers: Endgame (2023)’, Decimal(‘858.8’))
(‘Titanic (1997)’, Decimal(‘659.2’))
(‘The Dark Knight (2008)’, Decimal(‘535.4’))
(‘Toy Story 4 (2023)’, Decimal(‘434.9’))
(‘The Lion King (1994)’, Decimal(‘423.6’))
You may have noticed an additional challenge. We do this to clean up any remaining unread results. cursor.fetchall() .fetchmany()
Before executing any other statements on the same connection, you must clear any unread results. Otherwise, an exception is thrown. InternalError
JOIN Multiple TablesTo find out the names of the five highest-rated movies, run the following query:
select_movies_query = """ SELECT title, AVG(rating) as average_rating FROM ratings INNER JOIN movies ON chúng tôi = ratings.movie_id GROUP BY movie_id ORDER BY average_rating DESC LIMIT 5 """ with connection.cursor() as cursor: cursor.execute(select_movies_query) for movie in cursor.fetchall(): print(movie)OUTPUT
(‘Night of the Living Dead’, Decimal(‘9.90000’))
(‘The Godfather’, Decimal(‘9.90000’))
(‘Avengers: Endgame’, Decimal(‘9.75000’))
(‘Eternal Sunshine of the Spotless Mind’, Decimal(‘8.90000’))
(‘Beasts of No Nation’, Decimal(‘8.70000’))
You can find the name of the reviewer with the most ratings like this:
select_movies_query = """ SELECT CONCAT(first_name, " ", last_name), COUNT(*) as num FROM reviewers INNER JOIN ratings ON chúng tôi = ratings.reviewer_id GROUP BY reviewer_id ORDER BY num DESC LIMIT 1 """ with connection.cursor() as cursor: cursor.execute(select_movies_query) for movie in cursor.fetchall(): print(movie) ('Mary Cooper', 4)As you can see, most of the reviews were written by Mary Cooper.
The process of executing a query always remains the same: we pass the query to get the results using. cursor.execute() .fetchall()
Updating and deleting records from the databaseIn this section, we will update and remove some of the entries. We will select the required lines using a keyword. WHERE
UPDATE command
Imagine a reviewer Amy Farah Fowler is married to Sheldon Cooper. She changed her last name to Cooper and we need to update the database. To update records in MySQL, use the operator: UPDATE
update_query = """ UPDATE reviewers SET last_name = "Cooper" WHERE first_name = "Amy" """ with connection.cursor() as cursor: cursor.execute(update_query)Let’s say we want to allow reviewers to change grades. The program needs to know, and the new. SQL example: movie_id reviewer_id rating
UPDATE ratings SET rating = 5.0 WHERE movie_id = 18 AND reviewer_id = 15; SELECT * FROM ratings WHERE movie_id = 18 AND reviewer_id = 15; The specified queries first update the rating and then output the updated one. Let's write a Python script that will allow us to adjust the grades: modify_ratings.py from getpass import getpass from mysql.connector import connect, Error movie_id = input("Enter movie id: ") reviewer_id = input("Enter reviewer id: ") new_rating = input("Enter new rating: ") update_query = """ UPDATE ratings SET rating = "%s" WHERE movie_id = "%s" AND reviewer_id = "%s"; SELECT * FROM ratings WHERE movie_id = "%s" AND reviewer_id = "%s" """ % ( new_rating, movie_id, reviewer_id, movie_id, reviewer_id, ) try: with connect( host="localhost", user=input("Enter username: "), password=getpass("Enter password: "), database="online_movie_rating", ) as connection: with connection.cursor() as cursor: for result in cursor.execute(update_query, multi=True): if result.with_rows: print(result.fetchall()) except Error as e: print(e)OUTPUT
Enter movie id: 18
Enter reviewer id: 15
Enter new rating: 5
Enter username: root
Enter password: ········
[(18, 15, Decimal(‘5.0’))]
[(18, 15, Decimal(‘5.0’))]
To pass multiple requests to the same cursor, we assign a value to the argument. In this case, it returns an iterator. Each item in the iterator corresponds to a cursor object that executes the instruction passed in the request. The above code starts a loop on this iterator, calling for each cursor object. multi True cursor.execute() for .fetchall()
If no result set was obtained for the operation, then an exception is thrown. To avoid this error, in the code above, we use a property that indicates whether the last performed operation created rows. .fetchall() cursor.with_rows
While this code does the job, the instruction, as it stands, is a tempting target for hackers. It is vulnerable to a SQL injection attack that could allow attackers to corrupt or misuse the database. WHERE
For example, if the user submits, and as input, then the result would look like this: movie_id = 18 reviewer_id = 15 ratings = 5.0
$ python modify_ratings.py
Enter movie id: 18
Enter reviewer id: 15
Enter new rating: 5.0
Enter username:
Enter password:
[(18, 15, Decimal(‘5.0’))]
[(18, 15, Decimal(‘5.0’))]
The score for and changed too. But if you were a hacker, you could send a hidden command to the input: movie_id = 18 reviewer_id = 15 5.0
$ python modify_ratings.py
Enter movie id: 18
Enter reviewer id: 15″; UPDATE reviewers SET last_name = “A
Enter new rating: 5.0
Enter username:
Enter password:
[(18, 15, Decimal(‘5.0’))]
[(18, 15, Decimal(‘5.0’))]
Again, the output shows that the reported rating has been changed to 5.0. What changed?
The hacker intercepted the data update request. An update request will change all records in the reviewer’s table: last_name “A”
… SELECT first_name, last_name
… FROM reviewers
… “””
… cursor.execute(select_query)
… for reviewer in cursor.fetchall():
… print(reviewer)
…
(‘Chaitanya’, ‘A’)
(‘Mary’, ‘A’)
(‘John’, ‘A’)
(‘Thomas’, ‘A’)
(‘Penny’, ‘A’)
(‘Mitchell’, ‘A’)
(‘Wyatt’, ‘A’)
(‘Andre’, ‘A’)
(‘Sheldon’, ‘A’)
(‘Kimbra’, ‘A’)
(‘Kat’, ‘A’)
(‘Bruce’, ‘A’)
(‘Domingo’, ‘A’)
(‘Rajesh’, ‘A’)
(‘Ben’, ‘A’)
(‘Mahinder’, ‘A’)
(‘Akbar’, ‘A’)
(‘Howard’, ‘A’)
(‘Pinkie’, ‘A’)
(‘Gurkaran’, ‘A’)
(‘Amy’, ‘A’)
(‘Marlon’, ‘A’)
The above code displays and for all records in the table of reviewers. An SQL injection attack corrupted this table, changing all records to “A”. first_name last_name last_name
There is a quick solution to prevent such attacks. Do not add user-supplied query values directly to the query string. Better to update the script by sending request values as arguments to .execute()
modify_ratings.py from getpass import getpass from mysql.connector import connect, Error movie_id = input("Enter movie id: ") reviewer_id = input("Enter reviewer id: ") new_rating = input("Enter new rating: ") update_query = """ UPDATE ratings SET rating = %s WHERE movie_id = %s AND reviewer_id = %s; SELECT * FROM ratings WHERE movie_id = %s AND reviewer_id = %s """ val_tuple = ( new_rating, movie_id, reviewer_id, movie_id, reviewer_id, ) try: with connect( host="localhost", user=input("Enter username: "), password=getpass("Enter password: "), database="online_movie_rating", ) as connection: with connection.cursor() as cursor: for result in cursor.execute(update_query, val_tuple, multi=True): if result.with_rows: print(result.fetchall()) except Error as e: print(e)Note that placeholders are no longer enclosed in string quotes. verifies that the values in the tuple given as an argument are of the required data type. If the user tries to enter some problematic characters, the code will throw an exception: %s cursor.execute()
OUTPUT
$ python modify_ratings.py
Enter movie id: 18
Enter reviewer id: 15″; UPDATE reviewers SET last_name = “A
Enter new rating: 5.0
Enter username:
Enter password:
1292 (22007): Truncated incorrect DOUBLE value: ’15”;
This approach should always be used when you include user input in a request. Take the time to learn about other ways to prevent SQL injection attacks.
Deleting records: the DELETE command
The procedure for deleting records is very similar to updating them. Since this is an irreversible operation, we recommend that you first run the query with the same filter to ensure that you are deleting the records you want. For example, to remove all movie ratings, data, we can first run the appropriate query: DELETE SELECT reviewer_id = 7 SELECT
select_movies_query = """ SELECT reviewer_id, movie_id FROM rating WHERE review_id = 7 """ with connection.cursor() as cursor: cursor.execute(select_movies_query) for movie in cursor.fetchall(): print(movie)OUTPUT
(2, 7)
(2, 8)
(2, 12)
(2, 23)
The above code snippet displays a pair, and for entries in the table estimates, for which. After making sure that these are the records to be deleted, let’s execute the query with the same filter: reviewer_id movie_id reviewer_id = 2 DELETE
delete_query = "DELETE FROM ratings WHERE reviewer_id = 2" with connection.cursor() as cursor: cursor.execute(delete_query) Other ways to connect Python and MySQLIn this tutorial, we introduced the MySQL Connector / Python, which is the officially recommended means of interacting with a MySQL database from a Python application. Here are a couple of other popular connectors:
mysqlclient is a library that is a competitor to the official connector and is actively being supplemented with new functions. Since the core of the library is written in C, it has better performance than the official pure Python connector. The big drawback is that mysqlclient is quite difficult to set up and install, especially on Windows.
MySQLdb is legacy software that is still used in commercial applications today. Written in C and faster MySQL Connector / Python, but only available for Python 2.
These drivers act as interfaces between your program and the MySQL database. In fact, you just send your SQL queries through them. However, many developers prefer to use the object-oriented paradigm for data management, not SQL queries.
Object-relational mapping ( ORM ) is a process that allows not only the query but also the manipulation of data from a database directly usingOOPs. The ORM library encapsulates the code needed to manipulate data, freeing developers from the need to use SQL queries. Here are the most popular ORM libraries for combining Python and SQL:
SQLAlchemy is an ORM that simplifies communication between Python and other SQL databases. You can create different engines for different databases like MySQL, PostgreSQL, SQLite, etc.
peewee is a lightweight and fast ORM library with a simple configuration, which is very useful when your interaction with the database is limited to fetching a few records. If you need to copy individual records from a MySQL database to a CSV file, then peewee is the best choice.
The Django ORM is one of the most powerful parts of the Django web framework, allowing you to easily interact with a variety of SQLite, PostgreSQL, and MySQL databases. Many Django-based applications use the Django ORM for data modelling and basic queries, however, for more complex tasks, developers usually use SQLAlchemy.
ConclusionIn this tutorial, we took a look at how to integrate a MySQL database into your Python application. We also developed a test sample of the MySQL database and interacted with it directly from Python code. Python has connectors for other DBMSs such as MongoDB and PostgreSQL. We will be glad to know what other materials on Python and databases you would be interested in.
The media shown in this article are not owned by Analytics Vidhya and are used at the Author’s discretion.
Related
How To Create A Partition On Your Mac
Unlike Windows-based computers, Macs usually do not come with multiple hard-drive partitions. It is just a single partition that you see when you first boot your Mac. Though there is no harm using your Mac with a single partition, it is highly recommended that you have at least two partitions on your machine. This will let you store your important files on one partition when the other one is being formatted. It is also helpful if you plan to install another OS beside your Mac OS. Regardless of your reasons, here’s how you can do it.
Creating a Partition on a MacIt is really easy to create a partition on a Mac, as your Mac comes preloaded with an utility that allows you to do that. The utility we are talking about is called “Disk Utility” which is found in the Utilities folder on your Mac. Besides partitioning, it allows you to perform a number of disk-related operations on your machine.
Here are the steps you need to follow:
Name – Enter a name for your new partition, it could be anything of your choice. It’s suggested that you use a meaningful name, though.
Format – This one is important. Make sure to choose the appropriate format that you think will work with the files that you will be storing on this new partition.
Size – Specify the size for your new partition.
7. Restart your Mac when it is done.
Congrats! A new partition has been successfully created on your Mac, with all of your defined settings. You can now use it to store your files, videos, music, and all the content that you have been storing on your main hard drive until now.
ConclusionWhile it’s easy to use a Mac with just a single partition, adding another one decreases the chances of your files being corrupted in case you crash your machine.
Mahesh Makvana
Mahesh Makvana is a freelance tech writer who’s written thousands of posts about various tech topics on various sites. He specializes in writing about Windows, Mac, iOS, and Android tech posts. He’s been into the field for last eight years and hasn’t spent a single day without tinkering around his devices.
Subscribe to our newsletter!
Our latest tutorials delivered straight to your inbox
Sign up for all newsletters.
By signing up, you agree to our Privacy Policy and European users agree to the data transfer policy. We will not share your data and you can unsubscribe at any time.
Update the detailed information about How To Create A Pestle Analysis For Marketing Planning on the Kientrucdochoi.com website. We hope the article's content will meet your needs, and we will regularly update the information to provide you with the fastest and most accurate information. Have a great day!