Facebook Twitter Instagram
    TecAdmin
    • Home
    • FeedBack
    • Submit Article
    • About Us
    Facebook Twitter Instagram
    TecAdmin
    You are at:Home»Programming»Python»How to Connect Python with MySQL Database

    How to Connect Python with MySQL Database

    By RahulJanuary 10, 20235 Mins Read

    Python is a popular programming language that is widely used for web development, data analysis, scientific computing, and many other tasks. It is known for its simplicity and ease of use, making it a great choice for beginners and experienced developers alike. One of the key features of Python is its ability to interact with databases, which makes it easy to store, retrieve, and manipulate data.

    Advertisement

    In this article, we will look at how to connect to a MySQL database in Python using the `mysql-connector-python` library, which is a MySQL driver for Python. We will also cover some basic operations such as creating tables, inserting data, and querying the database.

    Prerequisites

    Before we begin, you will need to install the following:

    • Python 3: You can download and install Python from the official website (https://www.python.org/). Make sure to install the latest version of Python.
    • MySQL: You can download and install MySQL from the official website (https://www.mysql.com/). You will also need to create a database and set up a user with the appropriate permissions.
    • mysql-connector-python library: You can install this library using pip, the Python package manager. Open a terminal and run the following command:
      pip install mysql-connector-python 
      

    Connecting to the Database

    To connect to the MySQL database, we will need to import the `mysql.connector` module and create a connection object. The connection object will allow us to execute SQL queries and perform other operations on the database.

    Here is an example of how to create a connection object:

    1
    2
    3
    4
    5
    6
    7
    8
    9
    import mysql.connector
     
    # Connect to the database
    cnx = mysql.connector.connect(
        user='<username>',
        password='<password>',
        host='<hostname>',
        database='<database>'
    )

    Replace <username>, <<password>, <hostname>>, and <database> with your MySQL credentials and the name of the database you want to connect to.

    Once you have created the connection object, you can use it to execute SQL queries and perform other operations on the database.

    Creating Tables

    To create a table in the database, you can use the cursor object, which is used to execute SQL queries. First, you will need to create a cursor object and then use the `execute()` method to execute a `CREATE TABLE` statement.

    Here is an example of how to create a table:

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    # Create a cursor object
    cursor = cnx.cursor()
     
    # Create a table
    cursor.execute(
        '''
        CREATE TABLE users (
            id INTEGER PRIMARY KEY AUTO_INCREMENT,
            name VARCHAR(255) NOT NULL,
            email VARCHAR(255) NOT NULL
        )
        '''
    )

    This will create a table named users with three columns: id, name, and email. The id column is the primary key and will be automatically incremented for each new record. The name and email columns are both required and cannot be NULL.

    Inserting Data

    To insert data into a table, you can use the `INSERT INTO` statement. You can use the `execute()` method of the cursor object to execute the `INSERT INTO` statement and pass the values you want to insert as arguments.

    Here is an example of how to insert a new row into the `users` table:

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    # Insert a new row
    cursor.execute(
        '''
        INSERT INTO users (name, email)
        VALUES (%s, %s)
        ''',
        ('John Smith', '[email protected]')
    )
     
    # Commit the changes to the database
    cnx.commit()

    This will insert a new row into the `users` table with the name `John Smith` and email `[email protected]`.

    You can also insert multiple rows at once using the `executemany()` method of the cursor object. This method takes a list of tuples, where each tuple represents a row to be inserted.

    Here is an example of how to insert multiple rows:

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    # Insert multiple rows
    cursor.executemany(
        '''
        INSERT INTO users (name, email)
        VALUES (%s, %s)
        ''',
        [
            ('Jane Doe', '[email protected]'),
            ('Bob Smith', '[email protected]')
        ]
    )
     
    # Commit the changes to the database
    cnx.commit()

    Querying the Database

    To retrieve data from the database, you can use the `SELECT` statement. You can use the `execute()` method of the cursor object to execute a `SELECT` statement and retrieve the rows that match the query.

    Here is an example of how to retrieve all rows from the `users` table:

    1
    2
    3
    4
    5
    6
    7
    8
    9
    # Execute a SELECT statement
    cursor.execute('SELECT * FROM users')
     
    # Fetch all the rows
    rows = cursor.fetchall()
     
    # Print the rows
    for row in rows:
        print(row)

    This will retrieve all rows from the `users` table and print them to the console.

    You can also retrieve specific columns by specifying them in the `SELECT` statement. You can also use `WHERE` clauses and other SQL operators to filter the rows that are retrieved.

    Here is an example of how to retrieve specific columns and filter the rows:

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    # Execute a SELECT statement
    cursor.execute(
        '''
        SELECT name, email
        FROM users
        WHERE id > 2
        '''
    )
     
    # Fetch all the rows
    rows = cursor.fetchall()
     
    # Print the rows
    for row in rows:
        print(row)

    This will retrieve the name and email columns from the `users` table for all rows where the `id` is `greater than 2`.

    Closing the Connection

    Once you are done working with the database, it is important to close the connection to release the resources. You can do this by calling the `close()` method of the connection object.

    Here is an example of how to close the connection:

    1
    2
    # Close the connection
    cnx.close()

    Conclusion

    In this article, we have covered how to connect to a MySQL database in Python using the `mysql` database. By following the steps outlined in this guide, you can easily establish a connection to a MySQL database using the Python MySQL Connector library. First, you will need to install the library using pip. Then, you can use the `mysql.connector.connect()` function to establish a connection to the database by passing in the appropriate parameters such as host, username, password, and database name. Once you have established a connection, you can use the cursor object to execute SQL queries and retrieve data from the database. It is important to remember to close the connection and cursor when you are finished working with the database to prevent any errors or issues.

    MySQL Python
    Share. Facebook Twitter Pinterest LinkedIn Tumblr Email WhatsApp

    Related Posts

    How to Install Python 3.11 on Amazon Linux 2

    Installing Python 3.11 on Debian Linux

    How To Install Python 3.11 on Debian 11/10

    Installing Python 3.11 on Ubuntu, Debian and LinuxMint

    How To Install Python 3.11 on Ubuntu, Debian and LinuxMint

    Add A Comment

    Leave A Reply Cancel Reply

    Advertisement
    Recent Posts
    • What is the /etc/mtab File in Linux
    • The “Hello World” Challenge: Printing in 20 Different Programming Languages
    • How to Install PHP 8.2-7.4 on RHEL & CentOS Stream 9
    • How to Install MySQL 8.0 on RHEL & CentOS Stream 9
    • How to Split Large Archives in Linux using the Command Line
    Facebook Twitter Instagram Pinterest
    © 2023 Tecadmin.net. All Rights Reserved | Terms  | Privacy Policy

    Type above and press Enter to search. Press Esc to cancel.