My suggestion: login to your mysql database and try out the queries in your code. If you get an error, amend the code.
I tried the first 3 CREATE TABLE queries, below is what I got and what I changed. Number 3 worked 'as is' in mysql.
Quote:# doesn't work in mysql can't set TEXT UNIQUE get #1170 - BLOB/TEXT column 'class_name' used in key specification without a key length
CREATE TABLE IF NOT EXISTS classes (id INTEGER PRIMARY KEY, class_name TEXT UNIQUE NOT NULL)
# change to
CREATE TABLE IF NOT EXISTS classes (id INTEGER PRIMARY KEY, class_name VARCHAR(256) UNIQUE NOT NULL)
# ditto the above
CREATE TABLE IF NOT EXISTS users (id INTEGER PRIMARY KEY, username TEXT UNIQUE NOT NULL, password TEXT NOT NULL, role TEXT NOT NULL CHECK(role IN ('student', 'admin')))
# change to
CREATE TABLE IF NOT EXISTS users (id INTEGER PRIMARY KEY, username VARCHAR(256) UNIQUE NOT NULL, password VARCHAR(256) NOT NULL, role VARCHAR(256) NOT NULL CHECK(role IN ('student', 'admin')))
# Works fine in mysql
CREATE TABLE IF NOT EXISTS user_class_enrollment (
user_id INTEGER NOT NULL, class_id INTEGER NOT NULL,
PRIMARY KEY (user_id, class_id),
FOREIGN KEY (user_id) REFERENCES users (id) ON DELETE CASCADE,
FOREIGN KEY (class_id) REFERENCES classes (id) ON DELETE CASCADE
)
Now all you have to do is wade through all the other queries, trying them, see if they work, altering them if they don't according to the error you get. Personally, I don't feel like wading through 1126 lines of code, although, it is not really a difficult task.
Install phpmyadmin on your machine, log in, then you can try any mysql query you like, see if it works!
Ways to try to "automate the boring stuff": try loading your file as text and using .replace(). Maybe there are other similar "quick-fixes".Work on a copy though!
with open(mycode_copy) as infile:
text = infile.read()
text.replace("TEXT UNIQUE NOT NULL", "VARCHAR(256) UNIQUE NOT NULL")Good Luck!
