Python Forum
How to insert record into MySQL using Phython - Printable Version

+- Python Forum (https://python-forum.io)
+-- Forum: Python Coding (https://python-forum.io/forum-7.html)
+--- Forum: General Coding Help (https://python-forum.io/forum-8.html)
+--- Thread: How to insert record into MySQL using Phython (/thread-22997.html)



Unable to insert record into MySQL using Phython - abhay_kala - Dec-06-2019

Hi all,

Finally, I need help. I tried my best to solve issue but no result.Here is problem

Purpose: Insert Calculator's real calculation & result into MySQL database

Present Scenario: I have developed calculator in python 3.06 and it's working fine. It's showing result on screen. But, I want to store all record into MYSQL, which is not working I have MYSQL installed and connection between Python & MySQL is working (I ran a sample pgm and it's working)

Problem statement: My program is not inserting record into MYSQL

Expected result: When user insert calculation (e.g. 1+3) then result (4) then bother calculation & result must stored into MySql data base.

Please find my program here.

import pandas as pd

import numpy as np

import math

import mysql.connector



connection= mysql.connector.connect(host='localhost',

                               user='root', password='scott', database='calculator')



mySql_Create_Table_Query = """CREATE TABLE IF NOT EXISTS Records ( 

                             calculation varchar(250) NOT NULL,

                             Result  float  NOT NULL) """



cursor = connection.cursor()

result = cursor.execute(mySql_Create_Table_Query)

print()

print()

print()

print("                   welcome to scientific calculator")

print()

print()

print()

print("instructions")

print()

print('1) There should be space maintained in between value and operator')

print("eg: 1_*_2_+sin(1) where _ represents space")

print()

print("2) All trigonometric functions take input in radians")

print()

print("3) The functions you will enter are case sensitive")

print()

print("4) List of functions available are:")

print()

print("sin()               cos()               tan()")

print("sin()               cos()               tan()")



print("cot()               sec()               cosec()")



print("asin()               acos()               atan()")



print("acot()               asec()               acosec()")



print("asinh()               acosh()               atanh()")



print("sinh()               cosh()               tanh()")



print("ln()               log()               log2()")

 

print("log1p()               root()               cuberoot()")



print("square()               cube()               gif()")



print("ceil()               trunc()               fpf()")



print("mod()               signum()               pi()")



print("e()               degrees()               radian()")



print("!()               gamma()               lgamma()")



print("1/x()               frexp()               pi^2()")



print("e^2()               e^x-1()               10^x()")



print("e^x")

print()

print()



sentc1=np.array([1,1,1])



def number_checker(x):

    for i in range(len(x)):

        if(x[i]=='(' or x[i]==')'):

            return False

        if(x[i]=='^'):

            return False

    return True



def comma_checker(x):

    for i in range(len(x)):

        if(x[i]==','):

            return True



def e_checker(x):

    if(x=='pi^2'):



        z=int(sentc1[1])



        y=math.multiply(pi*pi)



#gives the value of pi^2



    elif(x=='e^2'):



        z=int(sentc1[1])

        y=math.multiply(e*e)



#gives the value of e^2



    elif(x=='10'):



        z=int(sentc1[1])



        y=math.pow(10,z)



#returns 10**x



    elif(x=='e'):

        

        z=int(sentc1[1])

        y=math.exp(z)



#returns the exponent



    return y



def checker(x):

    

    if(x=='sin'):



        z=int(sentc1[1][0:len(sentc1[1])-1])



        y=math.sin(z)



#returns the sine of given value



    elif(x=='cos'):



        z=int(sentc1[1][0:len(sentc1[1])-1])



        y=math.cos(z)



#returns the cosine of given value



    elif(x=='tan'):



        z=int(sentc1[1][0:len(sentc1[1])-1])



        y=math.tan(z)



#returns the tangent of given value



    elif(x=='cot'):



        z=int(sentc1[1][0:len(sentc1[1])-1])



        y=math.cot(z)



#returns the cotangent of given value



    elif(x=='sec'):



        z=int(sentc1[1][0:len(sentc1[1])-1])



        y=math.sec(z)



#returns the secant of given value



    elif(x=='cosec'):



        z=int(sentc1[1][0:len(sentc1[1])-1])



        y=math.cosec(z)



#returns the cosecant of given value



    elif(x=='asin'):



        z=int(sentc1[1][0:len(sentc1[1])-1])



        y=math.asin(z)



#returns the inverse sine of given value



    elif(x=='acos'):



        z=int(sentc1[1][0:len(sentc1[1])-1])



        y=math.acos(z)



#returns the inverse cosine of given value



    elif(x=='atan'):



        z=int(sentc1[1][0:len(sentc1[1])-1])



        y=math.atan(z)



#returns the inverse tangent of given value



    elif(x=='acot'):



        z=int(sentc1[1][0:len(sentc1[1])-1])



        y=math.acot(z)



#returns the inverse cotangent of given value



    elif(x=='asec' ):



        z=int(sentc1[1][0:len(sentc1[1])-1])



        y=math.asec(z)



#returns the inverse secant of given value



    elif(x=='acosec'):



        z=int(sentc1[1][0:len(sentc1[1])-1])



        y=math.acosec(z)



#returns the inverse cosecant of given value



    elif(x=='asinh'):



        z=int(sentc1[1][0:len(sentc1[1])-1])



        y=math.asinh(z)



#returns the inverse hyperbolic sine of given value



    elif(x=='acosh'):



        z=int(sentc1[1][0:len(sentc1[1])-1])



        y=math.acosh(z)



#returns the inverse hyperbolic cosine of given value



    elif(x=='atanh'):



        z=int(sentc1[1][0:len(sentc1[1])-1])



        y=math.atanh(z)



#returns the inverse hyperbolic tangent of given value



    elif(x=='sinh'):



        z=int(sentc1[1][0:len(sentc1[1])-1])



        y=math.sinh(z)



#returns the hyperbolic sine of given value



    elif(x=='cosh'):



        z=int(sentc1[1][0:len(sentc1[1])-1])



        y=math.cosh(z)



#returns the hyperbolic cosine of given value



    elif(x=='tanh'):



        z=int(sentc1[1][0:len(sentc1[1])-1])



        y=math.tanh(z)



#returns the hyperbolic tangent of given value



    elif(x=='ln'):



        z=int(sentc1[1][0:len(sentc1[1])-1])



        y=math.log(z)



#returns the natural logarithm of given value



    elif(x=='log'):



        z=int(sentc1[1][0:len(sentc1[1])-1])



        y=math.log10(z)



#returns the logarithm to base 10 of given value



    elif(x=='log2'):



        z=int(sentc1[1][0:len(sentc1[1])-1])



        y=math.log2(z)



#returns the logarithm to base 2 of given value



    elif(x=='log1p'):



        z=int(sentc1[1][0:len(sentc1[1])-1])



        y=math.log1p(z)



#returns the natural logarithm of 1+x



    elif(x=='root'):



        z=int(sentc1[1][0:len(sentc1[1])-1])



        y=math.sqrt(z)



#returns the square root of the value



    elif(x=='cuberoot'):



        z=int(sentc1[1][0:len(sentc1[1])-1])



        y=math.cbrt(z)



#returns the cube root of the value



    elif(x=='square'):



        z=int(sentc1[1][0:len(sentc1[1])-1])



        y=math.square(z)



#returns the square of the value



    elif(x=='cube'):



        z=int(sentc1[1][0:len(sentc1[1])-1])



        y=math.cube(z)



#returns the cube of the value



    elif(x=='gif'):



        z=int(sentc1[1][0:len(sentc1[1])-1])



        y=math.floor(z)



#returns the greatest integer less than or equal to x



    elif(x=='ceil'):



        z=int(sentc1[1][0:len(sentc1[1])-1])



        y=math.ceil(z)



#returns the smallest integer greater than or equal to x



    elif(x=='trunc'):



        z=int(sentc1[1][0:len(sentc1[1])-1])



        y=math.trunc(z)



#returns the truncated integer value of x



    elif(x=='fpf'):



        z=int(sentc1[1][0:len(sentc1[1])-1])



        y=math.modf(z)



#returns both gif and fpf in the forn of a tuple



    elif(x=='mod'):



        z=int(sentc1[1][0:len(sentc1[1])-1])



        y=math.fabs(z)



#returns the absolute value of x



    elif(x=='signum'):



        z=int(sentc1[1][0:len(sentc1[1])-1])



        y=math.signum(z)



#gives the signum value {-1,0,1}



    elif(x=='pi'):



        z=int(sentc1[1][0:len(sentc1[1])-1])



        y=math.pi(z)



#gives the value of pi



    elif(x=='e'):



        z=int(sentc1[1][0:len(sentc1[1])-1])



        y=math.e(z)



#gives the value of e



    elif(x=='degrees'):



        z=int(sentc1[1][0:len(sentc1[1])-1])



        y=math.degrees(z)



#Converts angle from radians to degrees



    elif(x=='radian'):



        z=int(sentc1[1][0:len(sentc1[1])-1])



        y=math.radians(z)



#Converts angle from degrees to radians



    elif(x=='!'):



        z=int(sentc1[1][0:len(sentc1[1])-1])



        y=math.factorial(z)



#returns the factorial of the given value (the value must be a whole number)



    elif(x=='gamma'):



        z=int(sentc1[1][0:len(sentc1[1])-1])



        y=math.gamma(z)



#Returns the Gamma function at x



    elif(x=='lgamma'):



        z=int(sentc1[1][0:len(sentc1[1])-1])



        y=math.lgamma(z)



#Returns the natural logarithm of the absolute value of the Gamma function at x



    elif(x=='1/x'):



        z=int(sentc1[1][0:len(sentc1[1])-1])



        y=math.reciprocal(z)



#returns reciprocal of given value



    elif(x=='frexp'):



        z=int(sentc1[1][0:len(sentc1[1])-1])



        y=math.frexp(z)



#returns the mantissa and exponent of x as the pair(m,e)



    return y

def assign(x):

    for i in range(len(x)):

        

        global sentc1

        global sum1

        

        if(i==0 and len(x[i])>=2):

            if(x[i][1]=='^'):

                

                sentc1=np.array(x[i].split('^'))

                sum1=e_checker(sentc1[0])



            elif(comma_checker(x[i])):



                x=np.array(x[i][x[i].index('(')+1:x[i].index(')')].split(','))

                z=int(sentc1[0])

                s=int(sentc1[1])

                sum1=double_checker(z,s)



            elif(number_checker(x[i])):



                w=float(x[i])

                sum1=w

                

            else:

            

                sentc1=np.array(x[i].split('('))

                sum1=checker(sentc1[0])



        if(len(x[i])>=2):



            if(x[i][1]=='^'):



                sentc1=np.array(x[i].split('^'))

                x[i]=e_checker(sentc1[0])



            elif(comma_checker(x[i])):



                sentc1=np.array(x[i][sentc[i].index('(')+1:x[i].index(')')].split(','))

                z=int(sentc1[0])

                s=int(sentc1[1])

                x[i]=double_checker(z,s)



            elif(number_checker(x[i])):



                w=float(x[i])

                x[i]=w

                

            else:

            

                sentc1=np.array(x[i].split('('))

                x[i]=checker(sentc1[0])

            

        if(i==0 and len(x[i])==1):



            sum1=float(x[i])



        elif(len(x[i])==1 and i%2==0):



            c=float(x[i])

            x[i]=c

    return x



q='a'



global sum1

global calculation 

while(q!='q'):

    print("enter your calculation")

    calculation=input()



    sentc=np.array(calculation.split())



    sentc_sub=assign(sentc)

    

    for i in range(len(sentc_sub)):

        if(sentc[i]=='+'):

            sum1=sum1+ float(sentc[i+1])

        elif(sentc[i]=='-'):

            sum1 =sum1- float(sentc[i+1])

        elif(sentc[i]=='*'):

            sum1=sum1 * float(sentc[i+1])

        elif(sentc[i]=='/'):

            sum1=sum1 / float(sentc[i+1])

        elif(sentc[i]=='%'):

            sum1=sum1 % float(sentc[i+1])

    print()

    print()

    print('ans is ',sum1)

    print(" Record inserted in database")

    mySql_insert_query = """INSERT INTO records (Calculation,Result)

                           VALUES (%s, %f) """

   # records_to_insert= [calculation,sum1]

    cursor.execute("mySql_insert_query", calculation, sum1 )

    connection.commit()

    print()

    print()

    print('if you want to quit press q else any other key to continue')

    q=input()

    



cursor.close()
Many thanks in advance


How to insert record into MySQL using Phython - abhay_kala - Dec-06-2019

Hi all,

Finally, I need help. I tried my best to solve issue but no result.Here is problem

Purpose: Insert Calculator's real calculation & result into MySQL database

Present Scenario: I have developed calculator in python 3.06 and it's working fine. It's showing result on screen. But, I want to store all record into MYSQL, which is not working I have MYSQL installed and connection between Python & MySQL is working (I ran a sample pgm and it's working)

Problem statement: My program is not inserting record into MYSQL

Expected result: When user insert calculation (e.g. 1+3) then result (4) then bother calculation & result must stored into MySql data base.

Please find my program here.

import pandas as pd

import numpy as np

import math

import mysql.connector



connection= mysql.connector.connect(host='localhost',

                               user='root', password='scott', database='calculator')



mySql_Create_Table_Query = """CREATE TABLE IF NOT EXISTS Records ( 

                             calculation varchar(250) NOT NULL,

                             Result  float  NOT NULL) """



cursor = connection.cursor()

result = cursor.execute(mySql_Create_Table_Query)

print()

print()

print()

print("                   welcome to scientific calculator")

print()

print()

print()

print("instructions")

print()

print('1) There should be space maintained in between value and operator')

print("eg: 1_*_2_+sin(1) where _ represents space")

print()

print("2) All trigonometric functions take input in radians")

print()

print("3) The functions you will enter are case sensitive")

print()

print("4) List of functions available are:")

print()

print("sin()               cos()               tan()")

print("sin()               cos()               tan()")



print("cot()               sec()               cosec()")



print("asin()               acos()               atan()")



print("acot()               asec()               acosec()")



print("asinh()               acosh()               atanh()")



print("sinh()               cosh()               tanh()")



print("ln()               log()               log2()")

 

print("log1p()               root()               cuberoot()")



print("square()               cube()               gif()")



print("ceil()               trunc()               fpf()")



print("mod()               signum()               pi()")



print("e()               degrees()               radian()")



print("!()               gamma()               lgamma()")



print("1/x()               frexp()               pi^2()")



print("e^2()               e^x-1()               10^x()")



print("e^x")

print()

print()



sentc1=np.array([1,1,1])



def number_checker(x):

    for i in range(len(x)):

        if(x[i]=='(' or x[i]==')'):

            return False

        if(x[i]=='^'):

            return False

    return True



def comma_checker(x):

    for i in range(len(x)):

        if(x[i]==','):

            return True



def e_checker(x):

    if(x=='pi^2'):



        z=int(sentc1[1])



        y=math.multiply(pi*pi)



#gives the value of pi^2



    elif(x=='e^2'):



        z=int(sentc1[1])

        y=math.multiply(e*e)



#gives the value of e^2



    elif(x=='10'):



        z=int(sentc1[1])



        y=math.pow(10,z)



#returns 10**x



    elif(x=='e'):

        

        z=int(sentc1[1])

        y=math.exp(z)



#returns the exponent



    return y



def checker(x):

    

    if(x=='sin'):



        z=int(sentc1[1][0:len(sentc1[1])-1])



        y=math.sin(z)



#returns the sine of given value



    elif(x=='cos'):



        z=int(sentc1[1][0:len(sentc1[1])-1])



        y=math.cos(z)



#returns the cosine of given value



    elif(x=='tan'):



        z=int(sentc1[1][0:len(sentc1[1])-1])



        y=math.tan(z)



#returns the tangent of given value



    elif(x=='cot'):



        z=int(sentc1[1][0:len(sentc1[1])-1])



        y=math.cot(z)



#returns the cotangent of given value



    elif(x=='sec'):



        z=int(sentc1[1][0:len(sentc1[1])-1])



        y=math.sec(z)



#returns the secant of given value



    elif(x=='cosec'):



        z=int(sentc1[1][0:len(sentc1[1])-1])



        y=math.cosec(z)



#returns the cosecant of given value



    elif(x=='asin'):



        z=int(sentc1[1][0:len(sentc1[1])-1])



        y=math.asin(z)



#returns the inverse sine of given value



    elif(x=='acos'):



        z=int(sentc1[1][0:len(sentc1[1])-1])



        y=math.acos(z)



#returns the inverse cosine of given value



    elif(x=='atan'):



        z=int(sentc1[1][0:len(sentc1[1])-1])



        y=math.atan(z)



#returns the inverse tangent of given value



    elif(x=='acot'):



        z=int(sentc1[1][0:len(sentc1[1])-1])



        y=math.acot(z)



#returns the inverse cotangent of given value



    elif(x=='asec' ):



        z=int(sentc1[1][0:len(sentc1[1])-1])



        y=math.asec(z)



#returns the inverse secant of given value



    elif(x=='acosec'):



        z=int(sentc1[1][0:len(sentc1[1])-1])



        y=math.acosec(z)



#returns the inverse cosecant of given value



    elif(x=='asinh'):



        z=int(sentc1[1][0:len(sentc1[1])-1])



        y=math.asinh(z)



#returns the inverse hyperbolic sine of given value



    elif(x=='acosh'):



        z=int(sentc1[1][0:len(sentc1[1])-1])



        y=math.acosh(z)



#returns the inverse hyperbolic cosine of given value



    elif(x=='atanh'):



        z=int(sentc1[1][0:len(sentc1[1])-1])



        y=math.atanh(z)



#returns the inverse hyperbolic tangent of given value



    elif(x=='sinh'):



        z=int(sentc1[1][0:len(sentc1[1])-1])



        y=math.sinh(z)



#returns the hyperbolic sine of given value



    elif(x=='cosh'):



        z=int(sentc1[1][0:len(sentc1[1])-1])



        y=math.cosh(z)



#returns the hyperbolic cosine of given value



    elif(x=='tanh'):



        z=int(sentc1[1][0:len(sentc1[1])-1])



        y=math.tanh(z)



#returns the hyperbolic tangent of given value



    elif(x=='ln'):



        z=int(sentc1[1][0:len(sentc1[1])-1])



        y=math.log(z)



#returns the natural logarithm of given value



    elif(x=='log'):



        z=int(sentc1[1][0:len(sentc1[1])-1])



        y=math.log10(z)



#returns the logarithm to base 10 of given value



    elif(x=='log2'):



        z=int(sentc1[1][0:len(sentc1[1])-1])



        y=math.log2(z)



#returns the logarithm to base 2 of given value



    elif(x=='log1p'):



        z=int(sentc1[1][0:len(sentc1[1])-1])



        y=math.log1p(z)



#returns the natural logarithm of 1+x



    elif(x=='root'):



        z=int(sentc1[1][0:len(sentc1[1])-1])



        y=math.sqrt(z)



#returns the square root of the value



    elif(x=='cuberoot'):



        z=int(sentc1[1][0:len(sentc1[1])-1])



        y=math.cbrt(z)



#returns the cube root of the value



    elif(x=='square'):



        z=int(sentc1[1][0:len(sentc1[1])-1])



        y=math.square(z)



#returns the square of the value



    elif(x=='cube'):



        z=int(sentc1[1][0:len(sentc1[1])-1])



        y=math.cube(z)



#returns the cube of the value



    elif(x=='gif'):



        z=int(sentc1[1][0:len(sentc1[1])-1])



        y=math.floor(z)



#returns the greatest integer less than or equal to x



    elif(x=='ceil'):



        z=int(sentc1[1][0:len(sentc1[1])-1])



        y=math.ceil(z)



#returns the smallest integer greater than or equal to x



    elif(x=='trunc'):



        z=int(sentc1[1][0:len(sentc1[1])-1])



        y=math.trunc(z)



#returns the truncated integer value of x



    elif(x=='fpf'):



        z=int(sentc1[1][0:len(sentc1[1])-1])



        y=math.modf(z)



#returns both gif and fpf in the forn of a tuple



    elif(x=='mod'):



        z=int(sentc1[1][0:len(sentc1[1])-1])



        y=math.fabs(z)



#returns the absolute value of x



    elif(x=='signum'):



        z=int(sentc1[1][0:len(sentc1[1])-1])



        y=math.signum(z)



#gives the signum value {-1,0,1}



    elif(x=='pi'):



        z=int(sentc1[1][0:len(sentc1[1])-1])



        y=math.pi(z)



#gives the value of pi



    elif(x=='e'):



        z=int(sentc1[1][0:len(sentc1[1])-1])



        y=math.e(z)



#gives the value of e



    elif(x=='degrees'):



        z=int(sentc1[1][0:len(sentc1[1])-1])



        y=math.degrees(z)



#Converts angle from radians to degrees



    elif(x=='radian'):



        z=int(sentc1[1][0:len(sentc1[1])-1])



        y=math.radians(z)



#Converts angle from degrees to radians



    elif(x=='!'):



        z=int(sentc1[1][0:len(sentc1[1])-1])



        y=math.factorial(z)



#returns the factorial of the given value (the value must be a whole number)



    elif(x=='gamma'):



        z=int(sentc1[1][0:len(sentc1[1])-1])



        y=math.gamma(z)



#Returns the Gamma function at x



    elif(x=='lgamma'):



        z=int(sentc1[1][0:len(sentc1[1])-1])



        y=math.lgamma(z)



#Returns the natural logarithm of the absolute value of the Gamma function at x



    elif(x=='1/x'):



        z=int(sentc1[1][0:len(sentc1[1])-1])



        y=math.reciprocal(z)



#returns reciprocal of given value



    elif(x=='frexp'):



        z=int(sentc1[1][0:len(sentc1[1])-1])



        y=math.frexp(z)



#returns the mantissa and exponent of x as the pair(m,e)



    return y

def assign(x):

    for i in range(len(x)):

        

        global sentc1

        global sum1

        

        if(i==0 and len(x[i])>=2):

            if(x[i][1]=='^'):

                

                sentc1=np.array(x[i].split('^'))

                sum1=e_checker(sentc1[0])



            elif(comma_checker(x[i])):



                x=np.array(x[i][x[i].index('(')+1:x[i].index(')')].split(','))

                z=int(sentc1[0])

                s=int(sentc1[1])

                sum1=double_checker(z,s)



            elif(number_checker(x[i])):



                w=float(x[i])

                sum1=w

                

            else:

            

                sentc1=np.array(x[i].split('('))

                sum1=checker(sentc1[0])



        if(len(x[i])>=2):



            if(x[i][1]=='^'):



                sentc1=np.array(x[i].split('^'))

                x[i]=e_checker(sentc1[0])



            elif(comma_checker(x[i])):



                sentc1=np.array(x[i][sentc[i].index('(')+1:x[i].index(')')].split(','))

                z=int(sentc1[0])

                s=int(sentc1[1])

                x[i]=double_checker(z,s)



            elif(number_checker(x[i])):



                w=float(x[i])

                x[i]=w

                

            else:

            

                sentc1=np.array(x[i].split('('))

                x[i]=checker(sentc1[0])

            

        if(i==0 and len(x[i])==1):



            sum1=float(x[i])



        elif(len(x[i])==1 and i%2==0):



            c=float(x[i])

            x[i]=c

    return x



q='a'



global sum1

global calculation 

while(q!='q'):

    print("enter your calculation")

    calculation=input()



    sentc=np.array(calculation.split())



    sentc_sub=assign(sentc)

    

    for i in range(len(sentc_sub)):

        if(sentc[i]=='+'):

            sum1=sum1+ float(sentc[i+1])

        elif(sentc[i]=='-'):

            sum1 =sum1- float(sentc[i+1])

        elif(sentc[i]=='*'):

            sum1=sum1 * float(sentc[i+1])

        elif(sentc[i]=='/'):

            sum1=sum1 / float(sentc[i+1])

        elif(sentc[i]=='%'):

            sum1=sum1 % float(sentc[i+1])

    print()

    print()

    print('ans is ',sum1)

    print(" Record inserted in database")

    mySql_insert_query = """INSERT INTO records (Calculation,Result)

                           VALUES (%s, %f) """

   # records_to_insert= [calculation,sum1]

    cursor.execute("mySql_insert_query", calculation, sum1 )

    connection.commit()

    print()

    print()

    print('if you want to quit press q else any other key to continue')

    q=input()

    



cursor.close()