Using SQLAlchemy ORM to create foreign Key association times error

When learning to use the SQLAlchemy module, step on a pit and share it.

Bury The hidden danger

I first created a Student information table with the following statement:

> CREATE TABLE student (    -> id INT UNSIGNED AUTO_INCREMENT,    -> name VARCHAR(20) NOT NULL,    -> age TINYINT,    -> PRIMARY KEY (id)    -> );

Table of 3 fields: self-increment ID (unsigned number, the self-increment ID will not be negative, of course, with unsigned, feel good Professional), name and age (this should also be unsigned).

An error occurred

After studying MySQL, I learned a bit about the Pymysql module. And finally, this orm,sqlachemy. This table is used to test a lot of commands and knowledge points, and then to get stuck when using sqlalchemy to establish a foreign key association. There were also SQL statements built with foreign key associations, and it went well.
Now there is a student information sheet, and then create a student score table. 3 fields: Exam name, student ID, score. The test name and student ID as the composite primary key, the student ID associated with the ID in the information table, do the foreign Key association:

from sqlalchemy import create_enginefrom sqlalchemy.ext.declarative import declarative_basefrom sqlalchemy import Column, Integer, Stringfrom sqlalchemy import ForeignKey  # 外键关联需要这个engine = create_engine("mysql+pymysql://admin:[email protected]/week12",                       encoding=‘utf-8‘, echo=True)Base = declarative_base()  # 生成orm基类class Student(Base):    __tablename__ = ‘student‘  # 表名，这张表不创建，可以写的简单点    id = Column(primary_key=True)  # 只要声明你需要的字段名，主键必须声明    name = Column()  # 字段类型可以不要，我们不是创建表    age = Column()class Exam(Base):    __tablename__ = ‘exam‘    name = Column(String(32), primary_key=True)    student_id = Column(Integer(), ForeignKey("student.id"), primary_key=True)  # 声明外键关联    score = Column(Integer, nullable=False)  # 规定不能为空Base.metadata.create_all(engine)  # 创建表

Let the error after. To start Echo, the first is that the SQL statements are generated normally:

CREATE TABLE exam2 (    name VARCHAR(32) NOT NULL,     student_id INTEGER NOT NULL,     score INTEGER NOT NULL,     PRIMARY KEY (name, student_id),     FOREIGN KEY(student_id) REFERENCES student (id))

After the long error content, look at the last line:

sqlalchemy.exc.InternalError: (pymysql.err.InternalError) (1005, "Can‘t create table ‘week12.exam2‘ (errno: 150)") [SQL: ‘\nCREATE TABLE exam2 (\n\tname VARCHAR(32) NOT NULL, \n\tstudent_id INTEGER NOT NULL, \n\tscore INTEGER NOT NULL, \n\tPRIMARY KEY (name, student_id), \n\tFOREIGN KEY(student_id) REFERENCES student (id)\n)\n\n‘] (Background on this error at: http://sqlalche.me/e/2j85)

Preliminary troubleshooting

The first thing that comes to mind is that the code is wrongly written and checked carefully. All are copy of the teacher's blog code, but also carefully read the video, carefully check. Found nothing wrong with writing.
There's definitely no problem with account permissions, because you've tried a lot of things with this account through Python, including creating tables.
The composite primary key is used here, and the example is a little bit different, so it's changed to a simple single primary key.
You can only do this with your own ability, and then search the Internet. The first thing to start with the error message is this: "Sqlalchemy.exc.InternalError: (Pymysql.err.InternalError) (1005," Can ' t create TABLE "to search the Web.) There is no clear discovery.
The last smart search engine helped me refine a set of keywords, one of which was "MySQL error 1005". Before always thought is the error of Python, now think of may be MySQL error, so use this search a bit.

Jump into a new pit

This time the keyword refinement is more accurate, found content seems to be more useful.
The first thing to find is this: "Foreign key duplicate, delete this table foreign key". Think, the previous use of SQL statements to practice the foreign key has been associated with a table, this time to take another table associated with this key, is not associated with it. I'm usually a novice who has only used a few select commands, and I don't understand that deeply. So one jumped into the pit and wandered around, and at last it was a good jump out of me.

Finally woke up

Go ahead and see why: "The foreign key field does not match the type of field you want to check for foreign key validation." In their own self-distrust, and to carefully look at their own code, to see if the type has been written, there is no statement. The type of the original table because it is not created, are I default, so try to write the whole, there is no use.
Then go to SQL with the DESC command to see the field type, SQL is like this:

Look at your own code in the integer () does not declare the length, so add. However, Pycharm shows an error. No matter, try, of course, or not.
Although this point is wrong, I have noticed this unsigned flag in the table I created the day before. Then try declaring the field declaration as unsigned in the code. Blogs are not in the video, they try to add in parentheses, add the outer layer of the column brackets can not after. Finally, you can only search the Internet.
This goal is very clear, is "sqlalchemy unsigned number". The goal is clear, but no one specifically speaks of it. Open Web page using Ctrl+f find finally found the usage:

from sqlalchemy.dialects.mysql import INTEGERid = Column(INTEGER(unsigned=True), primary_key=True)

Jump out of the pit

Find the right question, and finally the natural will be solved. The code to create the table is as follows:

 from sqlalchemy import create_enginefrom sqlalchemy.ext.declarative Import Declarative_basefrom sqlalchemy Import Column, Integer, stringfrom sqlalchemy import ForeignKey # Foreign Key association requires this from SQLAlchemy . Dialects.mysql Import Integer # To use an unsigned integer engine = create_engine ("mysql+pymysql://admin:[email protected]/ Week12 ", encoding= ' Utf-8 ', echo=true) base = Declarative_base () # Generate ORM Base Classes Class Student (base): __t ablename__ = ' Student ' # table name, this table is not created, can write simple point id = column (primary_key=true) # Just declare the field name you need, the primary key must declare name = Column () # Word Segment Type No, we're not creating a table. Age = Column () class Exam (Base): __tablename__ = ' Exam ' name = Column (String), primary_key=true ) student_id = Column (INTEGER (unsigned=true), ForeignKey ("Student.id"), Primary_key=true) # Declares the foreign key association score = column (I Nteger, Nullable=false) # stipulates that you cannot create a table  for empty Base.metadata.create_all (engine) # 

No wonder the lectures are good, I'm not going to copy them. I usually use the example is not good, I will change (the example used in the demo is the standard int type). And then it will be a moth. But fortunately, I can climb out.

Using SQLAlchemy ORM to create foreign Key association times error