In this Python MySQL tutorial we want to learn How to Connect MySQL Database with Python, so Python is powerful programming language and it has different libraries for working with MySQL database, in this article we want to talk about MySQL Connector.
First of all we need to install MySQL Connector for Python and you can use pip for that.
|
1 |
pip install mysql-connector-python |
After that we have installed the required library, and we need to import our modules.
|
1 2 |
import mysql.connector from mysql.connector import Error |
Now we can establish a connection to the MySQL database. for this we need to provide the necessary information, such as host name, port number, database name, username and password.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 |
import mysql.connector from mysql.connector import Error try: connection = mysql.connector.connect( host='localhost', port='3306', database='codeloop', user='root', password='' ) if connection.is_connected(): print('Connected to MySQL database') except Error as e: print(e) |
Run the code and this will be the result
Now that we have successfully connected to MySQL database, we can perform different operations on it. for example we can create a new table, insert data into an existing table or retrieve data from the database.
In here I am retrieving data from books table.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 |
import mysql.connector from mysql.connector import Error try: # Establish a connection to the MySQL database connection = mysql.connector.connect( host='localhost', port='3306', database='codeloop', user='root', password='' ) # Create cursor object using the connection cursor = connection.cursor() # Execute an SQL query to select all records from table cursor.execute('SELECT * FROM books') # Fetch all the rows returned by the executed query rows = cursor.fetchall() # Iterate over the fetched rows and print each row for row in rows: print(row) except Error as e: # Print any error that occurs during the connection or query execution print(e) |
In the above code, first of all we have created a cursor object using connection.cursor() method. after that we execute a SQL query to retrieve all the rows from the books table using cursor.execute() method. and lastly we fetch all the rows using cursor.fetchall() method and loop through them to print each row.
This will be the result
This is the complete example code that connects to MySQL database, creates a table, inserts some data and retrieves it:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 |
import mysql.connector from mysql.connector import Error # establish connection to the MySQL database try: connection = mysql.connector.connect( host='localhost', port='3306', database='codeloop', user='root', password='' ) if connection.is_connected(): print('Connected to MySQL database') except Error as e: print(e) # create table in the MySQL database try: cursor = connection.cursor() create_table_query = ''' CREATE TABLE IF NOT EXISTS books ( id INT AUTO_INCREMENT PRIMARY KEY, name VARCHAR(255) NOT NULL, author VARCHAR(255) NOT NULL, price VARCHAR(20) NOT NULL ) ''' cursor.execute(create_table_query) connection.commit() print('Table created successfully') except Error as e: print(e) # insert some data into the MySQL database try: cursor = connection.cursor() insert_query = ''' INSERT INTO books (name, author, price) VALUES (%s, %s, %s) ''' data = ('Python', 'Codeloop', '220') cursor.execute(insert_query, data) connection.commit() print('Data inserted successfully') except Error as e: print(e) # retrieve data from the MySQL database try: cursor = connection.cursor() select_query = ''' SELECT * FROM books ''' cursor.execute(select_query) rows = cursor.fetchall() for row in rows: print(row) except Error as e: print(e) # close the connection to the MySQL database connection.close() |
Subscribe and Get Free Video Courses & Articles in your Email