I am an getting error in MYSQL during inserting an integer value(Contact_No)

Question

I am creating a school project of cs on student management system and I am geting and "Out of range value for column 'Contact_No' at row 1". I don't know how to fix this error.This is error I am getting. Please help me to fix it.

#Modules
from prettytable import PrettyTable
import mysql.connector as mc

#MYSQL Connection
db = mc.connect(host = "localhost", user = "root", passwd = "1213")

#Creating Required Database
cursor = db.cursor()
cursor.execute("CREATE DATABASE IF NOT EXISTS student")
cursor.execute("USE student")

#Creating Required Table
cursor.execute('''CREATE TABLE IF NOT EXISTS record(
    Admission_No INTEGER,
    Name VARCHAR(20),
    Class INTEGER,
    Contact_No INTEGER,
    Fees INTEGER,
    Address VARCHAR(1000))
''')

#Add Fucntion
def add():
    Adm_No = int(input("Enter Admission Number: "))
    Name = input("Enter Student Name: ")
    Classs = int(input("Enter Student Class: "))
    Contact_No = int(input("Enter Student Contact No: "))
    Fees = int(input("Enter Student Fees: "))
    Address = input("Enter Student Address: ")
    
    cursor = db.cursor()

    sql = "INSERT INTO record (Admission_No, Name, Class, Contact_No, Fees, Address) VALUES(%s, %s, %s, %s, %s, %s)"
    value = (Adm_No, Name, Classs, Contact_No, Fees, Address)

    cursor.execute(sql, value)
    db.commit()

    print("Information Added Successfully...")
    main()

#View Fucntion
def view():
    cursor = db.cursor()
    cursor.execute("SELECT * FROM record")
    result = cursor.fetchall()

    t = PrettyTable(['Admission_No', 'Name', 'Class', 'Conact_No', 'Fees', 'Address'])

    for Admission_No, Name, Class, Contact_No, Fees, Address in result:
        t.add_row([Admission_No, Name, Class, Contact_No, Fees, Address])
    print(t)
    main()

#Update Fucntion
def update():
    print("-"*74)
    print("*"*12, "Please Select The Information You Want TO Update", "*"*12)
    print("-"*74)
    print("1. Name")
    print("2. Class")
    print("3. Contact_No")
    print("4. Fees")
    print("5. Address")
    print("-"*74)

    opt = int(input("Enter Your Choice"))

    if opt == 1:
        c = int(input("Enter Student Admission No: "))
        new_name = input("Enter New Name: ")
        cursor = db.cursor()
        cursor.execute("UPDATE record set Name = "+new_name+" WHERE Asmission_No = "+c+";")
        db.commit()
        print("Information Updated Successfully...")
        main()

    elif opt == 2:
        c = int(input("Enter Student Admission No: "))
        clas = input("Enter New Class: ")
        cursor = db.cursor()
        cursor.execute("UPDATE record set Class = "+clas+" WHERE Asmission_No = "+c+";")
        db.commit()
        print("Information Updated Successfully...")
        main()

    elif opt == 3:
        c = int(input("Enter Student Admission No: "))
        contact = input("Enter New Contact No: ")
        cursor = db.cursor()
        cursor.execute("UPDATE record set Contact_No = "+contact+" WHERE Asmission_No = "+c+";")
        db.commit()
        print("Information Updated Successfully...")
        main()

    elif opt == 4:
        c = int(input("Enter Student Admission No: "))
        fees = input("Enter New Fees: ")
        cursor = db.cursor()
        cursor.execute("UPDATE record set Fees = "+fees+" WHERE Asmission_No = "+c+";")
        db.commit()
        print("Information Updated Successfully...")
        main()

    elif opt == 5:
        c = int(input("Enter Student Admission No: "))
        address = input("Enter New Address: ")
        cursor = db.cursor()
        cursor.execute("UPDATE record set Address = "+address+" WHERE Asmission_No = "+c+";")
        db.commit()
        print("Information Updated Successfully...")
        main()

    else:
        print("Please Enter Valid Value...")
        main()

#Search Fucntion
def search():
    detail = int(input("Enter Admission No: "))
    cursor = db.cursor()
    cursor.execute("SELECT * FROM record WHERE Admission_No = "+detail+"")
    result = cursor.fetchall()

    t = PrettyTable(['Admission_No', 'Name', 'Class', 'Conact_No', 'Fees', 'Address'])

    for Admission_No, Name, Class, Contact_No, Fees, Address in result:
        t.add_row([Admission_No, Name, Class, Contact_No, Fees, Address])
    print(t)
    main()

#Delete Fucntion
def delete():
    detail = int(input("Enter Admission No: "))
    cursor = cursor.execute()
    cursor.execute("DELETE from student WHERE Admission_No = "+detail+"")
    db.commit()
    print("Information Deleted Successfully...")

#Main Menu
def main():
    print("-"*74)
    print("*"*18, "Welcome To Student Management System", "*"*18)
    print("-"*74)
    print("1. Add Record")
    print("2. View Record")
    print("3. Update Record")
    print("4. Seach Record")
    print("5. Delete Record")
    print("6. Exit")
    print("-"*74)

    ch = int(input("Enter Your Choice: "))
    if ch == 1:
        add()
    elif ch == 2:
        view()
    elif ch == 3:
        update()
    elif ch == 4:
        search()
    elif ch == 5:
        delete()
    elif ch == 6:
        exit()
    else:
        print("Please Enter Valid Value...")

#Login System
while True:
    print("-"*77)
    print("*"*35, "Login", "*"*35)
    print("-"*77)

    username = input("Enter Username: ")
    password = input("Enter Password: ")
    if username == 'Admin' and password == '1213':
        print("Loged In Successfully...")
        main()
    else:
        print("Invalid Uername and password. Please Try Again Later.")
        break

I viewed some youtube videos on this type of error but I didn't find anything. I tried to make some changes in

#Creating Required Table
cursor.execute('''CREATE TABLE IF NOT EXISTS record(
    Admission_No INTEGER,
    Name VARCHAR(20),
    Class INTEGER,
    Contact_No INTEGER(10),
    Fees INTEGER,
    Address VARCHAR(1000))
''')

By adding Contact_No INTEGER(10). But it didn't help either. Please help me to fix this. Thanks in advance.

The 10 in INTEGER(10) is the display size not the integer size see https://dev.mysql.com/doc/refman/8.0/en/integer-types.html — P.Salmon, Jan 02 '23 at 13:32
The signed integer type is a 32-bit data type; it only supports values up to 2147483647. The `(10)` doesn't do what you think it does. This is a common misunderstanding for MySQL users. See my answer here: https://stackoverflow.com/questions/3135804/types-in-mysql-bigint20-vs-int20/3135854#3135854 — Bill Karwin, Jan 02 '23 at 15:23

score 0 · Answer 1 · answered Jan 02 '23 at 11:54

Can you try running the INSERT query in the mysql workbench itself with the values that you're trying to execute and see whether you're getting the same Out of Range error?

First verify whether the length of the input value len(Contact_No) that you're getting for contact number is <= to the size you defined when you created the table. Looks like the value that you're trying to insert into Contact No column is exceeding the limit of its data length which you have predefined.

I am an getting error in MYSQL during inserting an integer value(Contact_No)

1 Answers1