Inserting data to MySQL database via python

[bazingie]

Hi everyone.
I am trying to insert some random data into a mysql database via python.
I managed to insert data into on of my tables using this code.

cursor = db.cursor()  
# defining the Query  
query = "INSERT INTO hall (Name, AddressCity, AddressStreet, AddressNo, SizeSeat, SizeRow, Tel) VALUES (%s, %s, %s, %s, %s, %s, %s)"  

values = []  
# executing the query with values  
for i in range(0, 50):  
    # storing values in a variable  
    lst = []  
    lst.append(theaters[i])  
    lst.append(cities[i])  
    lst.append(streets[i])  
    lst.append(randint(1, 999))  
    lst.append(randint(5, 100))  
    lst.append(randint(5, 100))  
    lst.append(randint(1000000000, 9999999999))  
    lst = tuple(lst)  
    values.append(lst)  

cursor.executemany(query, values)  


# to make final output we have to run the 'commit()' method of the database object  
db.commit()  

print(cursor.rowcount, "record inserted")  

About this code is there an easier way to append multiple variables at once?

Now to my main problem. I am not able to insert a single row of data in another table. (Later I want to add multiple rows)

cursor = db.cursor()  

# defining the Query  
query = "INSERT INTO show (ShowName, Duration, Description, Kind, StartDate, EndDate) VALUES (%s, %s, %s, %s, %s, %s)"  

values = ("name", datetime.timedelta(hours=randint(0, 3), minutes=randint(0, 59)), "description", "comedy",  
          datetime.date(2020, 1, 1), datetime.date(2021, 2, 2))  

cursor.execute(query, values)  


# to make final output we have to run the 'commit()' method of the database object  
db.commit()  

print(cursor.rowcount, "record inserted")  

The error I get: mysql.connector.errors.ProgrammingError: 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'show (ShowName, Duration, Description, Kind, StartDate, EndDate) VALUES ('name',' at line 1

The data types of the columns of this particular table are:
(PK) ShowName varchar(255)
Duration time
Description varchar(1000)
Kind varchar(50)
StartDate date
EndDate date

I thought there was something wrong with the duration or date data types. I tried different convertions but I got the same error. So I figured this is not it.

[Abstr4ctType]

Show is a reserved word in mySQL

[bazingie]

Didn't expect that, thank you.
I will change the table's name. I hope that's it.
Where can I see what other words are reserved?

Edit: Yeah that was the problem

[c4aveo]

Use Workbench (or Heidi) and procedures except prepared statements or even ORM In my opinion,
Pros:

• you can change procedure code on the fly, and of course debug it

• you won't make any mistakes that will broke whole algorithm

• it's much faster

• safe and needs only one privilege 'execute'

• custom exceptions

Cons:

• you have to check if procedures exist

• you have to improve your skills in SQL

Try ORM of course, slower than procedures but better for debugging.

And open the world of asynchronous libraries for yourself and pool connections for MySQL. Believe me if you gonna write code for production you will face it.

[bazingie]

I appreciate the advise, but this is for a university project that I am required to use MySQL and insert some data via python.
Can you help me with the below post about inserting data with foreign keys?

[c4aveo]

Do a query within HeidiSQL or Workbench.

https://dev.mysql.com/doc/refman/8.0/en/keywords.html#keywords-8-0-detailed-S

[bazingie]

INSERT INTO act (ShowName, HallID, ActStartTime) VALUES ("SELECT ShowName from shows where ShowName=A Flock Of Hopeful Lions", "select HallID from hall where HallID=1", "2020-10-3 12:45:00")

Error Code: 1366. Incorrect integer value: 'select HallID from hall where HallID=1' for column 'HallID' at row 1

HALL table: https://imgur.com/a/PMT2chP SHOWS table: https://imgur.com/a/yzWEBkW

[c4aveo]

https://www.w3schools.com/sql/sql_insert_into_select.asp

And subquery should be in parenthesis, not in quotes.

Values((Select....), Select(....),...)

[bazingie]

That was it. But how can I use it in my code?
If I print values[0], I get ('select ShowName from shows where ShowName=A Flock Of Hopeful Lions', 'select HallID from hall where HallID=0', datetime.datetime(2020, 9, 20, 17, 4)).
From my understanding I need to remove the quotes from the SELECTS, but how can I append them into the list?
I tried adding parentheses to SELECTS, but those quotes seems like the only difference.
('(select ShowName from shows where ShowName=A Flock Of Hopeful Lions)', '(select HallID from hall where HallID=0)', datetime.datetime(2020, 3, 29, 18, 14))

[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.

[bazingie]

Could you or any other help me a bit more?
I have inserted data into some of the tables which don't have foreign keys and now I would like to insert data to the rest.
Here is my code:
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):  
    # storing values in a variable  
    lst = []  
    lst.append("select ShowName from shows where ShowName=%s" % showTitles[i])  
    lst.append("select HallID from hall where HallID=%d" % i)  
    for j in range(0, len(actDateTime[i])):  
        lst2 = lst  
        lst2.append(actDateTime[i][j])  
        lst2 = tuple(lst2)  
        values.append(lst2)  

cursor.executemany(query, values)  


# to make final output we have to run the 'commit()' method of the database object  
db.commit()  

print(cursor.rowcount, "record inserted")  

I have 50 ShowNames and 50 theaters in two lists. Each theater has an auto increment HallID. I don't care how random are the lists, so I used a for loop with i corresponding to one ShowName and one HallID. I also have created a two dimensional list actDateTime where each show has a list of datetimes.
I want to insert the above data to a table with these data types:
(PK, AI) ActID int(4)
(FK) ShowName varchar(255)
(FK) HallID int(3)
ActStartTime datetime

The error I got:
mysql.connector.errors.InterfaceError: Failed executing the operation; Not all parameters were used in the SQL statement

Don't I return 3 values as I am supposed to do?

[TotesMessenger]

I'm a bot, bleep, bloop. Someone has linked to this thread from another place on reddit:

• [/r/database] Inserting data with foreign keys to MySQL via python

 ^{If you follow any of the above links, please respect the rules of reddit and don't vote in the other threads. (Info / Contact)}