In this Python MySQL tutorial we want to learn about Python MySQL Database Connection, 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='mydatabase', 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 book table.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 |
import mysql.connector from mysql.connector import Error try: connection = mysql.connector.connect( host='localhost', port='3306', database='mydatabase', user='root', password='' ) cursor = connection.cursor() cursor.execute('SELECT * FROM book') rows = cursor.fetchall() for row in rows: print(row) except Error as e: 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 book 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='mydatabase', user='myusername', password='mypassword' ) 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 customers ( id INT AUTO_INCREMENT PRIMARY KEY, name VARCHAR(255) NOT NULL, email VARCHAR(255) NOT NULL, phone 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 customers (name, email, phone) VALUES (%s, %s, %s) ''' data = ('John Doe', 'john.doe@example.com', '123-456-7890') 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 customers ''' 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() |
