How to use Python sqlalchemy

How to use Python sqlalchemy

2. Initialize the connection:

From sqlalchemy.ext.declarative import declarative_basefrom sqlalchemy import Column, Integer, String, ForeignKey, UniqueConstraint, Indexfrom sqlalchemy.orm import sessionmaker, relationshipfrom sqlalchemy import Create_engineimport Osbasedir = Os.path.abspath (Os.path.dirname (__file__)) # Create base class for object: Base = Declarative_base ()

2. Create a table:

# Create a single table
Class Users (Base):
    # table Name
    ' Users '
# Structure of the table:
primary_key=True)
Username = Column (String (32))
Password = Column (String (16))

初始化数据库：

__init__ (self, username, password):
    # Initialize Database
    Self.username = Username
Self.password = password

To create a federated unique index

# __table_args__ (
#     UniqueConstraint ("column name 1", "Column Name 2", "Federated Unique Index name"),
# index     ("Index name", "Column Name 1", "Column Name 2"),
#) #创建联合唯一索引
__table_args__ = (
    UniqueConstraint (name=' uix_id_name '),
Index (' password '),
)

3. Create a Map

# Create Mappings
Class Userormhelper (object):
    __init__ (self, database_name):
Database_path = Os.path.join (basedir, database_name)
' sqlite:///' + database_path
Self.engine = Create_engine (Sqlalchemy_database_uri)

Create a table

def create_db (self):
    # Create a table
    Base.metadata.create_all (self.engine)  # CREATE TABLE
Session = Sessionmaker (bind=self.engine)
Self.session = Session ()

Delete a table

def drop_db (self):
    # Delete a table
    Base.metadata.drop_all (self.engine)   #删除表

4. Next is a simple database of additions and deletions to the operation, these operations I was to facilitate the test installation of their own have made some changes, I will attach the test code later

Inserting data

Def addUser (Self, users):
    # Insert Data
    issucess=false 
  Usrslist=self.query_all_with_user_name_password (users) 
 if (usrslist and  Len (usrslist) >0):  #用户已经注册 
 Span style= "color: #808080; Font-style:italic "> return False 
  self.session.add (users) 
  Self.session.commit () 
 Issucess=true 
  return issucess            

Delete data

def delete (self,users):
    # Delete Data
    issucess=False
Self.session.query (Users). filter_by (username=users.username). First ()
Self.session.delete (User1)
Self.session.commit ()
issucess=True
Return issucess

Modify

(1) Matching username modification password

def Update_user_extra_by_user_name ( self, users): 
 # Match and modify password 
  issucess = false 
   Self.session.query (Users). Filter (Users.username = = users.username). Update ({" password ": Users.password}, synchronize_session= ' evaluate ') 
 self.session.commit () 
 Issucess=true 
  return issucess    

  (2) Match password modify username

def Update_user_name_by_user_extra ( self, users): 
 # matches and modifies name 
. Span style= "color: #808080; Font-style:italic "> issucess = false 
  self.session.query (Users). Filter (Users.password = = Users.password). Update ({ "username": Users.username}, synchronize_session=  evaluate ') 
 self.session.commit () 
 issucess= true 
  return Issucess 
              

Inquire

Ann Password Query all

def query_all_with_user_extra (self, users):
    # Querying the same password data
    Self.session.query (Users). filter_by (Password=users.password). All ()
Return userlist

Ann Username inquires all, but only takes one

def query_one (self,users):
    # Query the same name data, take a
    userlist=self.session.query (Users). filter_by (username=users.username). Limit (1). All ()
Self.session.commit ()
Return userlist

Ann username Query but only take the first piece of data

    def query_first_with_user_name (self, users):        # Query the same name data and take the first        userlist=self.session.query (users). Filter_by (Username=users.username). First ()        return userlist

Ann Username,password Query All

def query_all_with_user_name_password (self, users):
    # query All the same Name,password data
    userlist=self.session.query (Users). filter_by (username=users.username,Password=users.password). All ( )
Return userlist

Ann Username Query All

def query_all_with_user_name (self, users):
    # query All the same name data
    userlist=self.session.query (Users). filter_by (username=users.username). All ()
Return userlist

The following is the database test code, write test I use TDD

Import unittestfrom userorm Import users,userormhelpertest_db = ' test.db ' class Userormhelpertestcase (unittest. TestCase): def setUp (self): "", Set up a blank temp database before each test "" "Self.helper=userormhelper ("User.db") self.helper.drop_db () self.helper.create_db () def tearDown (self): "" "Destroy Blank tem P Database after all test "" "self.helper.drop_db () def test_add (self): # Insert Testing Issuccess=self.help  Er.adduser (Users ("Tome", "Tom Jobn") Self.asserttrue (issuccess) Issuccess=self.helper.adduser (Users ("Tome", "Tom Jobn")) Self.assertfalse (issuccess) def test_delete (self): # delete Test issuccess = Self.helper.ad Duser (Users ("Tome", "Tom Jobn")) Self.asserttrue (issuccess) issucess=self.helper.delete (Users ("Tome", "Tom J Obn ")) Self.asserttrue (issucess) def test_query_all (self): # query All the same name data test Issuccess=self.helper . AddUser (Users ("Tome", "Tom Jobn"))        Self.asserttrue (issuccess) Issuccess=self.helper.adduser (Users ("Tome", "Tom Jobn2")) self.asserttr         UE (issuccess) Issuccess=self.helper.adduser (Users ("Tome", "Tom Jobn3")) Self.asserttrue (issuccess) Userlist=self.helper.query_all_with_user_name (Users ("Tome", "Tom Jobn")) # Print UserList Self.assertequa L (Len (userlist), 3) def test_query_none (self): # Query the same name data, take the first Test Userlist=self.helper.query_first_wit H_user_name ("Tom", "Tom Job") Self.assertequal (Userlist,none) def test_query_one (self): # Query the same NAM e data, take a test issuccess = Self.helper.addUser (Users ("Tome", "Tom Jobn")) Self.asserttrue (issuccess) is Success = Self.helper.addUser (Users ("Tome", "Tom Jobn2")) Self.asserttrue (issuccess) issuccess = Self.helper . AddUser (Users ("Tome", "Tom Jobn3")) Self.asserttrue (issuccess) userlist = Self.helper.query_one (Users ("Tome        "," Tom Jobn4 "))Self.assertequal (Len (userlist), 1) def Test_revise_extra (self): issuccess = Self.helper.addUser (Users ("Tome", "t Om jobn ")) Self.asserttrue (issuccess) issuccess=self.helper.update_user_extra_by_user_name (Users (" Tome "," to Mupdate ")) Self.asserttrue (issuccess) userlist = Self.helper.query_all_with_user_name (Users (" Tome "," Tom Job ")) Self.assertequal (Len (userlist), 1) def test_recise_name (self): issuccess = Self.helper.addUser (Users ( "Tome", "Tom Jobn")) Self.asserttrue (issuccess) issuccess = Self.helper.update_user_name_by_user_extra (Users        ("Tomeupdate", "Tom Jobn")) Self.asserttrue (issuccess) userlist = Self.helper.query_all_with_user_name (Users ("Tomeupdate", "Tom Job")) s Elf.assertequal (Len (userlist), 1) if __name__ = = ' __main__ ': Unittest.main ()

How to use Python sqlalchemy