Basic orm operations under python (1) -- CRUD simple operations under Mysql (including source code DEMO), ormcrud

Basic orm operations under python (1) -- CRUD simple operations under Mysql (including source code DEMO), ormcrud

Recently I was planning to transfer my work environment to ubuntu. I suddenly found that ubuntu is suitable for me to access the Internet, receive emails, write blogs, and write programs, in addition to not fully adapting to command line and various permission management when I first came into contact, the apt-get command is quite convenient. Various strange errors in windows didn't appear in ubuntu, well, I will not talk nonsense. Today I will give a brief introduction to the operations of ORM to Mysql under python (Note: you must read the documentation on the official website !)

Refer: http://docs.sqlalchemy.org/en/latest/orm/tutorial.html

 

I. Prepare the environment

 

1. Install mysql-server (before that, prepare the Python environment)

2. Installing mysql-python is a bit difficult here. I did not directly use the apt-get command, but later I installed it successfully using pip.

~$ pip install mysql-python

3. Install sqlalchemy

After the environment is ready, install sqlalchemy

~$ pip install sqlalchemy

　　

 

If you have a similar problem in step 2 pip install mysql-python, you need to install connector for c in some environments. If you are an x64 environment, select x86, x64, install

 

 

Download list: http://dev.mysql.com/downloads/connector/c/6.0.html#downloads

 

Reference solution: http://stackoverflow.com/questions/1972259/cannot-open-include-file-config-win-h-no-such-file-or-directory-while-inst

After the environment is ready, let's take a general look at how to use the sqlalchemy ORM

 

Ii. Actual operations

1. Create an engine object

Here, the engine is similar to our connection string, which indicates the type of database, user name, password, address, and so on that you will connect to. Here, I use the mysql database as an example,

# -*- coding: UTF-8 -*-from sqlalchemy import create_enginefrom sqlalchemy.ext.declarative import declarative_basefrom sqlalchemy import Column, Integer, Stringfrom sqlalchemy.orm import sessionmakerengine = create_engine('mysql://root:password@127.0.0.1:3306/test?charset=utf8',echo=True)

For the writing of connection strings for other types of databases, refer:

Http://docs.sqlalchemy.org/en/rel_1_0/core/engines.html#sqlalchemy.create_engine

 

2. Define Mappings

#declare a Mapping,this is the class describe map to table columnBase = declarative_base()

 

3. Define the Connection Manager

#connect session to active the actionSession = sessionmaker(bind=engine)session = Session()

 

4. Table Structure and class structure ing

class Person(Base):    __tablename__ = 'Person'    Id = Column(Integer, primary_key=True,autoincrement=True)    Pname = Column(String,nullable=False,default='')    Address = Column(String,nullable=False,default='')    Age = Column(Integer,nullable=False,default=0)    def __repr__(self):        return 'the info is ID %s Pname is %s Address is %s and Age is %s' % \        (self.Id, self.Pname, self.Address,  self.Age)

 

Based on the above code, you can complete Mysql operations. The complete code is as follows:

# -*- coding: UTF-8 -*-__author__ = 'Bruce'from sqlalchemy import create_enginefrom sqlalchemy import Column, Integer, Stringfrom sqlalchemy.orm import sessionmakerfrom sqlalchemy.ext.declarative import declarative_base#declare the connecting to the serverengine = create_engine('mysql://account:password@127.0.0.1:3306/test?charset=utf8',echo=False)#declare a Mapping,this is the class describe map to table columnBase = declarative_base()#connect session to active the actionSession = sessionmaker(bind=engine)session = Session()class Person(Base):    __tablename__ = 'Person'    Id = Column(Integer, primary_key=True,autoincrement=True)    Pname = Column(String,nullable=False,default='')    Address = Column(String,nullable=False,default='')    Age = Column(Integer,nullable=False,default=0)    def __repr__(self):        return 'the info is ID %s Pname is %s Address is %s and Age is %s' % \        (self.Id, self.Pname, self.Address,  self.Age)if __name__ == '__main__':    #add one    p = Person(Pname='bruce', Address='beijing', Age=22)    session.add(p)    session.commit()    #query one    p_1 = session.query(Person).filter_by(Pname='bruce').first()    print p_1    #delete one    p_2 = session.query(Person).filter_by(Pname='bruce').first()    if p_2:        session.delete(p_2)        session.commit()    #edit one    p_3 = session.query(Person).filter_by(Pname='bruce').first()    if p_3:        p_3.Age = 55        session.commit()