Deleting lines in mysql through python

Question

I am trying to write a table in mysql through python:

I want to make table in MYSQL but delete the lines that are more than 3, in other words I just want 3 lines. for example

emp_no   Num1   Num2   Num3
1          1      2      3
2          1      2      3
3          1      2      3

These should be deleted

4          1      2      3
5          1      2      3
6          1      2      3
.
.
.

My code is:

from __future__ import print_function
import mysql.connector
from mysql.connector import errorcode



cnx = mysql.connector.connect(user='',
                              host='localhost')
cursor = cnx.cursor()

DB_NAME = 'DB'
cnx.database = DB_NAME 


TABLES = {}
TABLES['History'] = (
    "CREATE TABLE `History` ("
    "  `emp_no` int(11) NOT NULL AUTO_INCREMENT,"
    "  `Num1` text NOT NULL,"
    "  `Num2` text NOT NULL,"
    "  `Num3` text NOT NULL,"
    "  PRIMARY KEY (`emp_no`)"
    ") ENGINE=InnoDB")


####################33
##
Num1=1
Num2=2
Num3=3

add_employee = ("INSERT INTO History "
               "(Num1, Num2, Num3) "
               "VALUES (%s, %s, %s)")

data_employee = (str(Num1), str(Num2), str(Num3))
### Insert new employee
cursor.execute(add_employee, data_employee)

emp_no = cursor.lastrowid

### Make sure data is committed to the database
cnx.commit()

### *** I use this line for deleting the rows ***
query2 = ("Delete FROM History "
    "WHERE  emp_no >  %s")
cursor.execute(query2, ('3',))


# this part is used for printing the table elements    
query = ("SELECT emp_no, Num1, Num2, Num3 FROM History ")

cursor.execute(query)
#print("last_name")



for (emp_no, Num1, Num2, Num3) in cursor:
 print("{} {} {}".format(
     emp_no, Num1, Num2, Num3))



cursor.close()
cnx.close()
##

When I run this for example 10 times always the result is:

1 1 2
2 1 2
3 1 2

but when I comment out the part:

query2 = ("Delete FROM History "
        "WHERE  emp_no >  %s")
    cursor.execute(query2, ('3',))

what I see is interesting:

the lines more than 3 are still there in Mysql they were just not being shown before commenting out the delete query.

So How can I remove specific lines completely from mysql?

I am sorry I do not know a lot about mysql I thought it might be related — Reza Asgharzadeh
– Reza Asgharzadeh, Commented Oct 19, 2012 at 18:26
Are you getting this error somewhere in this code? If no, you should delete the tag — XORcist
– XORcist, Commented Oct 19, 2012 at 18:28

XORcist · Accepted Answer · 2012-10-19 18:23:41Z

2

It seems you would need to call commit for the DELETE operation to take effect permanently, as you are doing after the INSERT operation.

answered Oct 19, 2012 at 18:23

XORcist

4,36726 silver badges32 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Deleting lines in mysql through python

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related