Python MySQL Get Started

Last modified on: October 20, 2024

<div class="flex-shrink-0 flex flex-col relative items-end">
<div>
<div class="pt-0">
<div class="gizmo-bot-avatar flex h-8 w-8 items-center justify-center overflow-hidden rounded-full">
<h2 class="relative p-1 rounded-sm flex items-center justify-center bg-token-main-surface-primary text-token-text-primary h-8 w-8"><strong>Getting Started with Python and MySQL</strong></h2>
</div>
</div>
</div>
</div>
<div class="group/conversation-turn relative flex w-full min-w-0 flex-col agent-turn">
<div class="flex-col gap-1 md:gap-3">
<div class="flex max-w-full flex-col flex-grow">
<div class="min-h-[20px] text-message flex w-full flex-col items-end gap-2 whitespace-normal break-words [.text-message+&amp;]:mt-5" dir="auto" data-message-author-role="assistant" data-message-id="0e4a809c-9789-4249-9b71-f4be901487ac">
<div class="flex w-full flex-col gap-1 empty:hidden first:pt-[3px]">
<div class="markdown prose w-full break-words dark:prose-invert light">
<p>Integrating MySQL with Python allows you to manage databases easily and perform data operations. Here&rsquo;s a step-by-step guide to help you get started.</p>
<hr />
<h3><strong>1. Install MySQL Server</strong></h3>
<p>Before using MySQL with Python, ensure you have MySQL Server installed. You can download it from the <a href="https://dev.mysql.com/downloads/mysql/" target="_new" rel="noopener">MySQL website</a>.</p>
<h3><strong>2. Install Python and MySQL Connector</strong></h3>
<p>You need Python installed on your machine. You can download it from the <a href="https://www.python.org/downloads/" target="_new" rel="noopener">official Python website</a>.</p>
<p>To connect Python with MySQL, install the MySQL Connector:</p>
<div class="dark bg-gray-950 contain-inline-size rounded-md border-[0.5px] border-token-border-medium relative">
<div class="flex items-center text-token-text-secondary bg-token-main-surface-secondary px-4 py-2 text-xs font-sans justify-between rounded-t-md h-9">bash</div>
<div class="sticky top-9 md:top-[5.75rem]">
<div class="absolute bottom-0 right-2 flex h-9 items-center">
<div class="flex items-center rounded bg-token-main-surface-secondary px-2 font-sans text-xs text-token-text-secondary">
<pre class="language-python"><code>pip install mysql-connector-python</code></pre>
</div>
</div>
</div>
</div>
<h3><strong>3. Create a MySQL Database</strong></h3>
<ol>
<li>Open your MySQL command line or a MySQL client like MySQL Workbench.</li>
<li>Create a new database:</li>
</ol>
<div class="dark bg-gray-950 contain-inline-size rounded-md border-[0.5px] border-token-border-medium relative">
<div class="flex items-center text-token-text-secondary bg-token-main-surface-secondary px-4 py-2 text-xs font-sans justify-between rounded-t-md h-9">sql</div>
<div class="sticky top-9 md:top-[5.75rem]">
<div class="absolute bottom-0 right-2 flex h-9 items-center">
<div class="flex items-center rounded bg-token-main-surface-secondary px-2 font-sans text-xs text-token-text-secondary">
<pre class="language-python"><code>CREATE DATABASE my_database;</code></pre>
</div>
</div>
</div>
</div>
<ol start="3">
<li>Use the new database:
<pre class="language-python"><code>USE my_database;</code></pre>
</li>
</ol>
<div class="dark bg-gray-950 contain-inline-size rounded-md border-[0.5px] border-token-border-medium relative">
<div class="flex items-center text-token-text-secondary bg-token-main-surface-secondary px-4 py-2 text-xs font-sans justify-between rounded-t-md h-9">sql</div>
<div class="sticky top-9 md:top-[5.75rem]">
<div class="absolute bottom-0 right-2 flex h-9 items-center">
<div class="flex items-center rounded bg-token-main-surface-secondary px-2 font-sans text-xs text-token-text-secondary">
<pre class="language-python"><code>CREATE TABLE users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    age INT,
    gender ENUM('Male', 'Female') NOT NULL
);
</code></pre>
</div>
</div>
</div>
</div>
<ol start="4">
<li>Create a sample table:</li>
</ol>
<div class="dark bg-gray-950 contain-inline-size rounded-md border-[0.5px] border-token-border-medium relative">
<div class="flex items-center text-token-text-secondary bg-token-main-surface-secondary px-4 py-2 text-xs font-sans justify-between rounded-t-md h-9">sql</div>
<div class="sticky top-9 md:top-[5.75rem]">
<div class="absolute bottom-0 right-2 flex h-9 items-center">
<div class="flex items-center rounded bg-token-main-surface-secondary px-2 font-sans text-xs text-token-text-secondary">
<pre class="language-python"><code>import mysql.connector
from mysql.connector import Error
</code></pre>
</div>
</div>
</div>
</div>
<h3><strong>4. Write Python Code to Connect to MySQL</strong></h3>
<p>Here&rsquo;s how to connect to your MySQL database using Python:</p>
<h4><strong>Import Required Libraries</strong></h4>
<div class="dark bg-gray-950 contain-inline-size rounded-md border-[0.5px] border-token-border-medium relative">
<div class="flex items-center text-token-text-secondary bg-token-main-surface-secondary px-4 py-2 text-xs font-sans justify-between rounded-t-md h-9">
<pre class="language-python"><code>import mysql.connector
from mysql.connector import Error
</code></pre>
</div>
</div>
<h4><strong>Establish a Connection</strong></h4>
<div class="dark bg-gray-950 contain-inline-size rounded-md border-[0.5px] border-token-border-medium relative">
<div class="flex items-center text-token-text-secondary bg-token-main-surface-secondary px-4 py-2 text-xs font-sans justify-between rounded-t-md h-9">
<pre class="language-python"><code>def create_connection(host_name, user_name, user_password, db_name):
    connection = None
    try:
        connection = mysql.connector.connect(
            host=host_name,
            user=user_name,
            password=user_password,
            database=db_name
        )
        print("Connection to MySQL DB successful")
    except Error as e:
        print(f"The error '{e}' occurred")
    return connection

# Example connection
connection = create_connection("localhost", "your_username", "your_password", "my_database")
</code></pre>
</div>
</div>
<h3><strong>5. Basic Database Operations</strong></h3>
<h4><strong>Insert Data</strong></h4>
<div class="dark bg-gray-950 contain-inline-size rounded-md border-[0.5px] border-token-border-medium relative">
<div class="flex items-center text-token-text-secondary bg-token-main-surface-secondary px-4 py-2 text-xs font-sans justify-between rounded-t-md h-9">
<pre class="language-python"><code>def insert_user(connection, name, age, gender):
    insert_user_query = f"""
    INSERT INTO users (name, age, gender) VALUES ('{name}', {age}, '{gender}');
    """
    cursor = connection.cursor()
    try:
        cursor.execute(insert_user_query)
        connection.commit()
        print("User inserted successfully")
    except Error as e:
        print(f"The error '{e}' occurred")

# Insert a user
insert_user(connection, "Alice", 30, "Female")
</code></pre>
</div>
</div>
<h4><strong>Query Data</strong></h4>
<div class="dark bg-gray-950 contain-inline-size rounded-md border-[0.5px] border-token-border-medium relative">
<div class="flex items-center text-token-text-secondary bg-token-main-surface-secondary px-4 py-2 text-xs font-sans justify-between rounded-t-md h-9">
<pre class="language-python"><code>def fetch_users(connection):
    fetch_users_query = "SELECT * FROM users;"
    cursor = connection.cursor()
    cursor.execute(fetch_users_query)
    results = cursor.fetchall()
    for row in results:
        print(row)

# Fetch users
fetch_users(connection)
</code></pre>
</div>
</div>
<h4><strong>Update Data</strong></h4>
<div class="dark bg-gray-950 contain-inline-size rounded-md border-[0.5px] border-token-border-medium relative">
<div class="flex items-center text-token-text-secondary bg-token-main-surface-secondary px-4 py-2 text-xs font-sans justify-between rounded-t-md h-9">
<pre class="language-python"><code>def update_user_age(connection, user_id, new_age):
    update_user_query = f"""
    UPDATE users SET age = {new_age} WHERE id = {user_id};
    """
    cursor = connection.cursor()
    try:
        cursor.execute(update_user_query)
        connection.commit()
        print("User age updated successfully")
    except Error as e:
        print(f"The error '{e}' occurred")

# Update a user's age
update_user_age(connection, 1, 31)
</code></pre>
</div>
</div>
<h4><strong>Delete Data</strong></h4>
<div class="dark bg-gray-950 contain-inline-size rounded-md border-[0.5px] border-token-border-medium relative">
<div class="flex items-center text-token-text-secondary bg-token-main-surface-secondary px-4 py-2 text-xs font-sans justify-between rounded-t-md h-9">
<pre class="language-python"><code>def delete_user(connection, user_id):
    delete_user_query = f"DELETE FROM users WHERE id = {user_id};"
    cursor = connection.cursor()
    try:
        cursor.execute(delete_user_query)
        connection.commit()
        print("User deleted successfully")
    except Error as e:
        print(f"The error '{e}' occurred")

# Delete a user
delete_user(connection, 1)
</code></pre>
</div>
</div>
<h3><strong>6. Closing the Connection</strong></h3>
<p>Make sure to close the connection when you&rsquo;re done:</p>
<div class="dark bg-gray-950 contain-inline-size rounded-md border-[0.5px] border-token-border-medium relative">
<div class="flex items-center text-token-text-secondary bg-token-main-surface-secondary px-4 py-2 text-xs font-sans justify-between rounded-t-md h-9">
<pre class="language-python"><code>if connection.is_connected():
    connection.close()
    print("Connection closed")
</code></pre>
</div>
</div>
<h3><strong>Conclusion</strong></h3>
<p>In this guide, you learned how to:</p>
<ul>
<li>Install MySQL and the necessary Python connector.</li>
<li>Create a database and table in MySQL.</li>
<li>Perform basic operations such as inserting, querying, updating, and deleting records using Python.</li>
</ul>
<p>This foundational knowledge will help you integrate MySQL into your Python applications effectively.</p>
</div>
</div>
</div>
</div>
</div>
</div>

Web Technology View More