Python Classroom Series – 28/Jul/2021

Installing mysql and mysql workbench

  • Install mysql Refer Here
  • Refer basic queries from mysql Refer Here
  • Now login into mysql workbench and connect to the local mysql server as shown in the class and
    • Create a database learning create database learning
    • Execute other SQL Statements
      use learning;
      create table Products ( id int, name varchar(255), description varchar(1024), price float, quantity int);
      Insert into Products (id, name, description, price, quantity) values (1, 'iphone 12 pro max', 'iphone 12 pro max from Apple', 120000, 100); 
      select * from Products;
    
  • Now lets assume we want to write code from python to create a table and insert some records into it.
  • In python there is no standard library available to work with mysql database, Python has a large community of libraries called as PyPI (Python Package Index) where there are lot of python libraries which we can use in our application
  • But for that we need a tool called pip
pip --version
  • Now lets find a package for working with mysql. The package is PyMySQL Refer Here Preview
  • We have install the PyMySQL and written the code to fetch the records from database and we were successful in that
import pymysql
import pymysql.cursors

# Connect to the database
connection = pymysql.connect(host='localhost',
                             user='root',
                             password='learning**123',
                             database='learning',
                             cursorclass=pymysql.cursors.DictCursor)

with connection:
    #with connection.cursor() as cursor:
    #    sql = "Insert into `Products` (`id`, `name`, `description`, `price`, `quantity`) values (2, 'iphone 12', 'iphone 12 from Apple', 90000, 100);"
    #    cursor.execute(sql)
    #    result = cursor.fetchone()
    #    print(result)

    with connection.cursor() as cursor:
        # Read a single record
        sql = "SELECT * FROM `Products`"
        cursor.execute(sql)
        result = cursor.fetchone()
        print(result)
        

Leave a Comment

This site uses Akismet to reduce spam. Learn how your comment data is processed.

About learningthoughtsadmin