In this Python tutorial we want to learn about Python SQL Integration, so Python and SQL are two powerful tools in the world of data analytics and management. Python is a powerful programming language with different libraries that you can easily use for database functionalities. SQL on the other hand is standard language for managing relational databases. integrating these two tools can provide powerful capabilities for data analysts, scientists and engineers.
In this article we want to talk that how to integrate Python and SQL, and some of the advantages of this.
Python SQL Integration
Python offers several libraries for connecting to SQL databases.
| PyODBC | This is Python module, using that you can connect to any database that has an ODBC driver installed. |
| SQLAlchemy | It is SQL toolkit and Object-Relational Mapping (ORM) library for Python, It provides a set of high-level APIs for connecting to different databases. |
| pymysql | It is pure Python MySQL driver that allows you to interact with MySQL databases from Python. |
First we need to install pymysql, and we can use pip for that like this pip install pymysql. after that we can use this code to create a table for us, we already have created our database at name of codeloop.
Now this is the complete code
|
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 |
import pymysql # Open database connection db = pymysql.connect( host="localhost", user="root", password="", database="codeloop" ) # Prepare cursor object cursor = db.cursor() # Create books table 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 DECIMAL(10, 2) )''' try: cursor.execute(create_table_query) print("Table 'books' created successfully") except pymysql.MySQLError as e: print(f"Error creating table: {e}") # SQL query to insert data into the books table insert_query = """INSERT INTO books (name, author, price) VALUES (%s, %s, %s)""" # Data to be inserted books_data = [ ('Python', 'Codeloop', 10.99), ('Django', 'Codeloop.org', 8.99), ('PyQt6', 'Parwiz', 12.99) ] try: # Execute the SQL query for each book for book in books_data: cursor.execute(insert_query, book) # Commit the transaction db.commit() print("Data inserted successfully") except pymysql.MySQLError as e: # Rollback in case of error db.rollback() print(f"Error inserting data: {e}") # Execute SQL query to fetch data cursor.execute("SELECT * FROM books") # Fetch all rows rows = cursor.fetchall() for row in rows: print("ID : " + str(row[0])) print("Name : " + row[1]) print("Author : " + row[2]) print("Price : " + str(row[3])) print() # Close database connection db.close() |
This Python code uses pymysql library to interact with a MySQL database. It starts by creating a connection to MySQL database at name of codeloop hosted on the local machine. after that cursor object is created to execute SQL commands.
This code first defines and executes an SQL query for creating a table called books if it doesn’t already exist. This table includes columns for id (auto-incremented primary key), name, author and price. If the table creation is successful, a confirmation message is printed; otherwise, an error message is shown.
After that this code prepares an SQL insert query and a list of book data to be inserted into the books table. It iterates over the list, executing the insert query for each book. If all inserts are successful, the transaction is committed; if any errors occur, the transaction is rolled back and an error message is printed.
After inserting the data, the script executes an SQL query to select all records from the books table. It fetches and prints all rows, displaying the id, name, author, and price for each book.
And lastly above code closes the cursor and the database connection to clean up resources.
Run the code and this will be the result
Subscribe and Get Free Video Courses & Articles in your Email