Seamless integration between the ORM framework and the mysql database.

Seamless integration between the ORM framework and the mysql database.

ORM
Now let's talk about in-depth database operations.
As we all know, if you use native database statements to write data, you have to repeat the code in a crazy way first, and basically do not reuse the data. They are all written once, and you need to write again later, in this case, the core of object programming is to teach you how to face object databases. In this case, let's look at the results.
When creating a table in a common SQL database:

1 create table table_name(id int not null auto_increment,2                         name varchar(32),3                         password varchar(32),4                          primary key(id)5                          )

This is a very simple SQL table. What is the role of orm in complicated tables? What is the association between them and the estimation header? It is a framework that encapsulates the database primitive. We can play with the database by Object-Oriented means.

Import sqlalchemyfrom sqlalchemy. ext. declarative import declarative_basefrom sqlalchemy import create_enginefrom sqlalchemy import Colum, Integer, Stringfrom sqlalchemy. orm import sessionmakerengine = create_engine ('mysql + pymysql: // root: 123456 @ localhost/slkdb', encoding = 'utf-8', echo = True) ''' note writing, orm supports many excuses. I use pymysql here, so it is mysql + pymysql: // <user >:< password >@< host>/<dbname> # user is the user name, password is the password, and host is the database IP address. dbname is the name of the data table to be processed. What does your data table not support Chinese characters? Error? What is Latin's error? I tell you, you only need to add one after dbname? Charset = utf8: engine = create_engine ('mysql + pymysql: // root: 123456 @ localhost/slkdb? Charset = utf8', encoding = 'utf-8', echo = True) because I am python3 and cannot install the MySQL-Python module, the corresponding creation of this interface will not be written. '''Base = declarative_base () # generate an orm Base class User (Base): _ tablename __= 'user' # table name id = Colum (Integer, primary_key = True) name = Colum (String (32) password = Colum (String (32) Base. metadata. create_all (engine) # create table structure session_class = sessionmaker (bind = engine) # create a database session. Note that only session = session_class () is created here. # The instance is generated at this time, equivalent to cursorUser1 = User (name = 'hehe ', password = '000000') # generate the data object User2 = User (name = 'zhaff', password = '000000 ') # generate the data object session to be generated. add (User1) session. add (User2) session. commit () # The data is created only after submission.

　　

Okay, the data is also inserted into the running result information:
--------------------------------------------

15:49:45, 082 INFO sqlalchemy. engine. base. Engine show variables like 'SQL _ Mode'
15:49:45, 082 INFO sqlalchemy. engine. base. Engine {}
15:49:45, 085 INFO sqlalchemy. engine. base. Engine select database ()
15:49:45, 085 INFO sqlalchemy. engine. base. Engine {}
15:49:45, 085 INFO sqlalchemy. engine. base. Engine show collation where 'charset' = 'utf8' and 'colation' = 'utf8 _ bin'
15:49:45, 085 INFO sqlalchemy. engine. base. Engine {}
15:49:45, 086 INFO sqlalchemy. engine. base. Engine select cast ('test plain returns 'as char (60) AS anon_1
15:49:45, 086 INFO sqlalchemy. engine. base. Engine {}
15:49:45, 087 INFO sqlalchemy. engine. base. Engine select cast ('test unicode returns' as char (60) AS anon_1
15:49:45, 087 INFO sqlalchemy. engine. base. Engine {}
15:49:45, 087 INFO sqlalchemy. engine. base. Engine select cast ('test collated returns 'as char character set utf8) COLLATE utf8_bin AS anon_1
15:49:45, 087 INFO sqlalchemy. engine. base. Engine {}
15:49:45, 088 INFO sqlalchemy. engine. base. Engine DESCRIBE 'user'
15:49:45, 088 INFO sqlalchemy. engine. base. Engine {}
15:49:45, 091 INFO sqlalchemy. engine. base. Engine BEGIN (implicit)
15:49:45, 092 INFO sqlalchemy. engine. base. Engine insert into user (name, password) VALUES (% (name) s, % (password) s)
456456 15:49:45, 092 INFO sqlalchemy. engine. base. Engine {'name': 'hei', 'Password': '2016 '}
15:49:45, 092 INFO sqlalchemy. engine. base. Engine insert into user (name, password) VALUES (% (name) s, % (password) s)
298789 15:49:45, 092 INFO sqlalchemy. engine. base. Engine {'name': 'zhaff', 'Password': '2016 '}
2013-03-06 15:49:45, 093 INFO sqlalchemy. engine. base. Engine COMMIT

-------------------------------------------------------------------
''' Note: the following error may occur during running: Warning: (1366, "Incorrect string value: '\ xD6 \ xD0 \ xB9 \ xFA \ xB1 \ xEA... 'For column' VARIABLE _ value' at row 481 ")
Result = self. _ query (query)
This warning or other alerts from balabara can be ignored at this time, because it is only a warning, not an error, the program is still running normally, and the data in the database table has been created. Technology is limited. This problem is not solved yet. I will have the opportunity to optimize ''' in the future '''
You may see how this makes it more complicated. After all, there are various types of object creation in various packet modules that seem complicated,

Well, it's time to show the real technology:

The added data has already been said. Just add the data above and query the data below.

Data = session. query (User ). filter (User. id> = 1 ). filter (User. id <4 ). all () # multi-condition query. Multiple filters can be used directly. The symbols here are compared, and the single equal sign will fail ~

Print (data)

You can also use in _ ([* arg]) to indicate matching in the arg tuples.

data = session.query(User).filter(User.name.in_(['zhaoxin','zhaff'])).all()print(data)

-------------
Output: [zhaoxin: 29999, zhaoxin: 29999, zhaff: 298789]

Modify:
Data = session. query (User ). filter (User. name = 'hehes '). all () [0] # multi-condition query. Here all [0] can also be replaced by frist ().
Print (data)
Data. name = 'SB'
Session. commit () # The modification must be submitted. Otherwise, it cannot be modified. Generally, a single modification is large, and multiple modifications can be used.
Print (data)
Output result:
------------------------
Hehe: 456456
Sb: 456456
------------------

Similarly, deleting is delete.
Data = session. query (User ). filter (User. name = 'SB '). all () [0] # multi-condition query. Here all [0] can also be replaced by frist ().
Print (data)
Data. name = 'SB'
Session. commit ()
In this way, the data named 'SB 'is deleted.

You can delete, modify, and query all the modifications you have made before querying them. Do not confuse them.

Group statistics

Statistics are all known, count, so here is naturally

Data = session. query (User ). filter (User. name = 'SB '). count () # if it does not have all, writing all is equivalent to a class. In this case, count is changed to a class method. The error message is that the attribute of count is not found in the User class.

Group statistics
From sqlalchemy import func
Data = session. query (User. name, func. count (User. name). group_by (User. name). all ()
Print (data)
-----------
Output: [('zhaff', 1), ('zhaoxin', 2)]
Group_by is the same as the group by command line, details step: http://www.cnblogs.com/Jason504327775/p/8512953.html

Join Table query:

If a new class object is created.
Class Bala (Base ):
***** This is omitted
Same as above

Then we need to associate two of the two tables. For example, if the column name in the table is the same, all the names will be available to me.
Result = session. query (User, Bala ). filter (User. name = Bala. name ). all () # This type of contact is forcibly specified, and no foreign key contact is required.
The output result is in the list of tuples. each tuple corresponds to the details of the two data items corresponding to User. name = Bala. name. This will not be demonstrated.
You can use a foreign key contact.
Result = session. query (User). join (Bala). all () #
The most awesome thing about the following orm is the convenience of foreign key Association, which can be directly written into the Code:

Import sqlalchemyfrom sqlalchemy. ext. declarative import declarative_basefrom sqlalchemy import create_enginefrom sqlalchemy import Column, Integer, String, ForeignKeyfrom sqlalchemy. orm import sessionmaker, relationshipengine = create_engine ('mysql + pymysql: // root: 123456 @ localhost/slkdb', encoding = 'utf-8') Base = declarative_base () # generate an orm Base class Student (Base): # regenerate two data tables. All data tables have been deleted and restarted. _ tablename __= 'student '# Table Name Id = Column (Integer, primary_key = True) name = Column (String (32), nullable = False) register_date = Column (String (32), nullable = False) def _ repr _ (self): return 'id: % s, name: % s' % (self. id, self. name) class Stu_record (Base): _ tablename __= 'stdred' id = Column (Integer, primary_key = True) day = Column (Integer) statue = Column (String (32), nullable = False) stu_id = Column (Integer, ForeignKey ('student. id') # associate Foreign keys Note that table_name is the name. id instead of the Class Object Name. id student = relationship ('student ', backref = 'my _ study_recode') # This implements reverse query. This line of code is equivalent to instantiating a Student object instance. In this case, the following self. student. name can be called. You can use my_study_recode to call the attributes of the Stu_record object. This function is called from memory, encapsulated in python, and is not a function in the database, this is much easier than def _ repr _ (self): return '% s login status % s' % (self. student. name, self. day, self. statue) Base. metadata. create_all (engine) # create the table structure session_class = sessionmaker (bind = engine) ses Sion = session_class () s1 = Student (name = 'zhaoxin', register_date = '2017-02-14 ') s2 = Student (name = 'jianji ', register_date = '1970-01-14 ') s3 = Student (name = 'ruizi', register_date = '1970-05-14') s4 = Student (name = 'luoli ', register_date = '1970-06-11 ') t1 = Stu_record (day = 1, statue = 'yes', stu_id = 1) t2 = Stu_record (day = 2, statue = 'no ', stu_id = 1) t3 = Stu_record (day = 3, statue = 'yes', stu_id = 1) t4 = Stu_record (day = 4, statue = 'no ', Stu_id = 1) t5 = Stu_record (day = 5, statue = 'yes', stu_id = 2) session. add_all ([s1, s2, s3, s4, t1, t2, t3, t4, t5]) session. commit () s_obj = session. query (Student ). filter (Student. name = 'zhaoxin '). first () # The returned result is the print (s_obj.my_study_recode) class of student. # through association, this association is the ing between classes in the memory and all attributes in Stu_record are retrieved, and call the _ repr _ function of Stu_record.

　　

----------------
[Logon status of zhaoxin in 1st days: YES; logon status of zhaoxin in 2nd days: NO; logon status of zhaoxin 3rd days: YES; logon status of zhaoxin 4th days: NO]

One-to-multiple foreign key associations are similar, but pay attention to the following details:
When associating, we all know that stu_id = Column (Integer, ForeignKey ('student. id '), so this one-to-many represents that such code has more than one line, and the associated keys are different;
There are several pieces of code similar to student = relationship ('student ', backref = 'my _ study_recode').
Foreign_keys = [*** this is the attribute name of the foreign key of the object. ***] As a result, the computer will be able to identify who is associated with the object, so that it will not be confused, if you write this into the function, the computer will be confused and an error will be reported: there are multiple foreign_keys associated in this table. The foreign_keys attribute should be specified, otherwise, you will be confused.

Okay. Next, let's talk about the most important multi-to-Multi-Association.

Directly explain in the Code and recreate the new table. (For the purpose of demonstration, I did not open it separately. As a qualified developer, it is really inappropriate to create and modify a file such as query in a code, but here is the demo, and I will write it together .)

Import sqlalchemyfrom sqlalchemy. ext. declarative import declarative_basefrom sqlalchemy import create_engine, Tablefrom sqlalchemy import Column, Integer, String, ForeignKeyfrom sqlalchemy. orm import sessionmaker, relationshipengine = create_engine ('mysql + pymysql: // root: 123456 @ localhost/slkdb? Charset = utf8') Base = declarative_base () book_m2m_author = Table ('book _ m2m_author ', Base. metadata, Column ('book _ id', Integer, ForeignKey ('book. id'), Column ('author _ id', Integer, ForeignKey ('author. id ') # Why not use the class method to create this table? Because I will not insert any data or change it in it. I only need to change the source data to the two classes, so the ing will automatically change the class Book (Base ): _ tablename __= 'book' id = Column (Integer, primary_key = True) name = Column (String (32) put_date = Column (String (32 )) authors = relationship ('author', secondary = book_m2m_author, backref = 'books ') # connect to the Author table. query def _ repr _ (self) through the third table book_m2m_author): return self. nameclass Author (Base): _ tablename __= 'author' id = Column (Integer, prima Ry_key = True) name = Column (String (32) def _ repr _ (self): return self. nameBase. metadata. create_all (engine) session_class = sessionmaker (bind = engine) session = session_class () b1 = Book (name = 'holy ghost + qindi ', put_date = '2017-6-12 ') b2 = Book (name = 'changshengjie + I want to seal Tian', put_date = '2017-2-19 ') b3 = Book (name = 'tomb + demons ', put_date = '2017-10-11 ') b4 = Book (name = 'sin inverse + Yinian impermanence', put_date = '2017-6-12 ') b5 = Book (name = 'dado + shadow Day', put_date = '2017-8-2 ') b6 = Book (Name = 'three-in-One ', put_date = '1970-3-7') a1 = Author (name = 'chen Dong') a2 = Author (name = 'ear root ') a3 = Author (name = 'tang Jia San Shao ') b1.authors = [a1, a3] # The third table associates the tables author and book in this way. Is the implementation of the secondary attribute in authors in the Book class. B2.authors = [a1, a2] b3.authors = [a1, a2] b4.authors = [a2, a3] b5.authors = [a3, a1] b6.authors = [a1, a2, a3] session. add_all ([b1, b2, b3, b4, b5, b6, a1, a2, a3]) session. commit () author_obj = session. query (orm_m2m.Author ). filter (orm_m2m.Author.name = 'chen Dong '). first () print (author_obj.books) # Use book To Call The lookup effect matching the Chen Dong attribute in Book. book_obj = session. query (orm_m2m.Book ). filter (orm_m2m.Book.name = 'Three in one '). first () print (book_obj, book_obj.authors) # in this way, the association between the author's book query and the author's book query is realized.

　　

---------------------------------
Output:
[Holy Ghost + qindi, Changsheng community + I want to seal the sky, the tomb of God + seeking for magic, fighting the mainland + hiding the sky, three in one]
Three in one [Chen Dong, Ear root, Tang Jia sanshao]

Well, here we have finished talking about the orm. In real life, there will be many associations between multiple tables. If you look at it, I'm sorry, maybe I'm not very good at it, I cannot explain myself, but my life is too short. I use python.