Code forHow to Use MySQL Database in Python Tutorial

using-mysql-connector.py

import mysql.connector as mysqlfrom tabulate import tabulate# insert MySQL Database information hereHOST = "localhost"DATABASE = ""USER = "root"PASSWORD = ""# connect to the databasedb_connection = mysql.connect(host=HOST, database=DATABASE, user=USER, password=PASSWORD)# get server informationprint(db_connection.get_server_info())# get the db cursorcursor = db_connection.cursor()# get database informationcursor.execute("select database();")database_name = cursor.fetchone()print("[+] You are connected to the database:", database_name)# create a new database called librarycursor.execute("create database if not exists library")# use that database cursor.execute("use library")print("[+] Changed to `library` database")# create a tablecursor.execute("""create table if not exists book (    `id` integer primary key auto_increment not null,    `name` varchar(255) not null,    `author` varchar(255) not null,    `price` float not null,    `url` varchar(255)    )""")print("[+] Table `book` created")# insert some booksbooks = [    {        "name": "Automate the Boring Stuff with Python: Practical Programming for Total Beginners",        "author": "Al Sweigart",        "price": 17.76,        "url": "https://amzn.to/2YAncdY"    },    {        "name": "Python Crash Course: A Hands-On, Project-Based Introduction to Programming",        "author": "Eric Matthes",        "price": 22.97,        "url": "https://amzn.to/2yQfQZl"    },    {        "name": "MySQL for Python",        "author": "Albert Lukaszewski",        "price": 49.99,    }]# iterate over books listfor book in books:    id = book.get("id")    name = book.get("name")    author = book.get("author")    price = book.get("price")    url = book.get("url")    # insert each book as a row in MySQL    cursor.execute("""insert into book (id, name, author, price, url) values (        %s, %s, %s, %s, %s    )    """, params=(id, name, author, price, url))    print(f"[+] Inserted the book: {name}")# commit insertiondb_connection.commit()# fetch the databasecursor.execute("select * from book")# get all selected rowsrows = cursor.fetchall()# print all rows in a tabular formatprint(tabulate(rows, headers=cursor.column_names))# close the cursorcursor.close()# close the DB connectiondb_connection.close()

Join 50,000+ Python Programmers & Enthusiasts like you!

Subscribe

Tags

New Tutorials

Popular Tutorials