0

I'm having trouble with this particular SQL insertion I'm performing using MySQLdb on Python. The particular schema for the table I'm attempting to insert into has been created with the following constraints:

Movie_Ratings (mid: integer, uid: integer, rating: integer, comments: string, timestamp: timestamp)

CREATE TABLE Movie_Ratings (
uid INT,
mid INT,
rating INT,
comments VARCHAR(200),
timestamp TIMESTAMP NOT NULL,
PRIMARY KEY (uid, mid),
FOREIGN KEY (uid) REFERENCES Users ON DELETE CASCADE,
FOREIGN KEY (mid) REFERENCES Movies ON DELETE CASCADE,
CONSTRAINT “ch_movie_rating” CHECK (rating BETWEEN 1 AND 5) )

Performing a SQL insert has worked for tables which do not have non-key constraints and timestamps. Here is the code:

import MySQLdb
import time
import random
import datetime
from datetime import datetime

def strTimeProp(start, end, format1, format2, prop):
    stime = time.mktime(time.strptime(start, format1))
    etime = time.mktime(time.strptime(end, format1))

    ptime = stime + prop * (etime - stime)

    return time.strftime(format2, time.localtime(ptime))


def randomDate(start, end, prop):
    return strTimeProp(start, end, '%m/%d/%Y %I:%M %p','%Y-%m-%d   %H:%M:%S', prop)

db = MySQLdb.connect("localhost","username","password","database")
x = db.cursor()
movie_command = "insert into Movie_Ratings  (mid,uid,rating,comments,timestamp) values(%s, %s, %s, %s, %s)"
movie_data = []
comments = ["I really like this movie","My son really enjoyed this movie","A family movie indeed","Bravo!","Impeccable character progression","Ingenious movie direction",
    "Not appropriate for the whole family","Too Romanticized","Kids didn't enjoy the movie","The director should go back to director school"]
#Number of movies in table        
for j in range(1,5043):
    #Random number of ratings/comments for each movie
    for i in range(1,random.randint(1,10)):
            tup = (j,random.randint(1,5),random.randint(1,5),random.choice(comments),randomDate("1/1/2008 1:30 PM","10/31/2016 4:50 AM", random.random()))
            movie_data.append(tup)

try:
        x.executemany(movie_command,movie_data)
        db.commit()
except:
        db.rollback()
db.close()

When printing out movie_data, both rating is between 1 and 5, and the timestamp is in the exact format of YYYY-mm-dd HH:MM:SS. I've attempted storing it as a datetime object and as a string, neither worked.

Any suggestions? I do not know why the data here cannot be inserted into the database! Thanks!

Yash Sharma
  • 33
  • 5
  • `import time ; time_stamp = time.time()` will work for you. – dsgdfg Nov 28 '16 at 09:12
  • I am attempting to fill up the database with timestamps between 1/1/2008 and 10/31/2016. I do not want the current time, I want to calculate a timestamp and push into the database. The format is exactly what's desired, I try pushing as a datetime object or a string and it doesn't work... – Yash Sharma Nov 29 '16 at 01:07
  • I attempted returning datetime.datetime.fromtimestamp(ptime).strftime(format2) instead of time.strftime(format2, time.localtime(ptime)) to no avail – Yash Sharma Nov 29 '16 at 01:21
  • Who say use currient time ? [Check this .](http://stackoverflow.com/questions/8022161/python-converting-from-datetime-datetime-to-time-time) – dsgdfg Nov 29 '16 at 05:51

0 Answers0