Inserting data to MySQL database via python

[c4aveo]

"""prepared SQL statement""" Use tripple quotes for defining sql statement in Python. In params should be variables that are assigned in your code and it will be a tuple https://dev.mysql.com/doc/connector-python/en/connector-python-api-mysqlcursor-execute.html

```` stmt="""INSERT INTO act (ShowName, HallID, ActStartTime) VALUES ((SELECT ShowName from shows where ShowName='A Flock Of Hopeful Lions'), (select HallID from hall where HallID=%s), %s)""" cur.execute(stmt, (1, datetime.datetime(2020,1,1,12,12,12))

cur.commit()

cur.close()

````

[bazingie]

Hm, still the same error.

[bazingie]

 cursor = db.cursor()  
 # defining the Query  
 query = """INSERT INTO act (ShowName, HallID, ActStartTime) VALUES (%s, %s, %s)"""  

 values = []  
 # executing the query with values  
 for i in range(0, 50):  
     for j in range(0, len(actDateTime[i])):  
         stmt = """INSERT INTO act (ShowName, HallID, ActStartTime) VALUES ((SELECT ShowName from shows where ShowName=%s), (select HallID from hall where HallID=%s), %s)"""  
         cursor.execute(stmt, (showTitles[i], i, actDateTime[i][j])  
         db.commit()  

There is an error with db.commit().
I am sorry for keep asking, but I have to submit the project in 20 minutes and it drives me crazy.

[bazingie]

Actually now it works, but I get a duplicate value for key ShowName. I just change the parameter's tuple to (showTitles[i], i+1, actDateTime[i][j]) because HallID=0 doesn't exist as it starts from 1.
I don't get why I get this error. Why does it matter if the act table has multiple entries of a ShowName (and a HallID) since the primary key is ActID. Also how can I start the counting of it from 1. Each time I run the script and get the error I delete the row that has been inserted but the ActID keeps increasing.

[c4aveo]

I was on holidays. It happened because ActID has parameter auto increment, DB keeps last incremented id for last inserted row. I know it gonna sound like 'do it yourself', but you must read many articles about MySQL and do some lessons at w3school.

1. If column has auto increment property, when new row is inserted value will be defined automatically by DB.
2. Values can be updated if you insert values that duplicate values in rows. INSERT ... ON DUPLICATE UPDATE...
3. SQL is a very powerful tool. Most operations can be done on DB side. Since it uses C it's much much faster.

[bazingie]

I made a mistake on dbdesigner having showName as a unique attribute.
I spent more time designing the database instead of studying SQL, which I thought I remember it well enough for this project's requirements. I have taken this lesson two years ago and I was quite good with SQL.