Python connection database using SQLAlchemy

Reference Python core programming

ORM (Object relational Mapper), if you are a programmer who prefers to manipulate Python objects rather than SQL queries, and still want to use a relational database as your backend, you might prefer to use ORM.

ORM takes a pure SQL statement and implements it as an object in Python, so that you manipulate these objects just as you would with SQL statements--tables in a database are converted to Python classes, where data columns are attributes, and database operations are used as methods. In general, ORM does a lot of work in lieu of you, it complicates things, or more code than using adapters directly. However, this extra work can achieve higher productivity.

Currently, the most well-known Python orm is SQLAlchemy and Sqlobject. The latter currently only supports Python2 versions.

In this section, we discuss the use of SQLAlchemy. SQLAlchemy Help Documentation

SQLAlchemy does not need to be manually installed in Python's standard library, where the PIP installation method is used:

PS c:\users\wc> pip install sqlalchemycollecting sqlalchemy  downloading https:// files.pythonhosted.org/packages/c1/c8/392fcd2d01534bc871c65cb964e0b39d59f/sqlalchemy-1.2.7.tar.gz (5 . 6MB)     100% |████████████████████████████████| 5.6MB 299kb/sinstalling collected Packages:sqlalchemy  for  sqlalchemy ... donesuccessfully Installed SQLAlchemy-1.2.7

The following script simply implements a sqlalchemy connection to the MySQL and SQL Server user shuffling application. When you run the script, note that we use the script written in the previous section!!!

#This script is compatible with python2.x and 3.x versions, using SQLAlchemy ORM to connect to SQL Server or MySQL, to achieve a user shuffle application fromOs.pathImportdirnameImportPymysql fromPymysql.errImportInternalerror fromRandomImportRandrange fromDistutils.logImportwarn as printf fromSQLAlchemyImportColumn,integer,string,create_engine,exc,orm fromSqlalchemy.ext.declarativeImportDeclarative_base#Import Local Application module (USHUFFLE_DBU) necessary constants and tool functions to avoid copying and pasting the same code everywhere fromUshuffle_dbuImportDbname,namelen,randname,fields,tformat,cformat,setup#Dialect+driver://username:[email protected]:p ort/databaseDSNs = {    'MySQL':'Mysql+pymysql://root:[email protected]/%s'%DBNAME,'Sqllite':'sqllite:///:memory:',    'SQL Server':'Mssql+pymssql://sa:[email protected]/%s'%DBNAME,}#the declaration layer of the SQLAlchemy. Base =declarative_base ()classUsers (Base):#inherit base class    __tablename__='Users' #mapped database table nameLogin =Column (String (Namelen)) UserID= Column (Integer,primary_key =True) ProjId=Column (Integer)def __str__(self):#returns a string format for easy-to-read data rows        return "'. Join (Map (Tformat, (Self.login,self.userid,self.projid)))classSqlalchemytest (object):#Initialize the function to get as much as possible a usable database, save its connection    def __init__(SELF,DSN):Try: ENG= Create_engine (DSN)#, echo = True) #尝试使用dsn创建数据库引擎, Echo is set to true to allow us to see the SQL statements generated by the ORMprintf'* * * * creation of database engine succeeded')        exceptImporterror:RaiseRuntimeError ()#creation failed. In general, engine creation failure means that SQLAlchemy does not support the selected database and throws Importerror        Try: Eng.connect ()#If the database engine is created successfully, try to create a database connectionprintf'* * * Connection to database%s succeeded'%DBNAME)except(exc. Internalerror,internalerror):#if creating a connection fails, it generally means that the database itself is unreachable. This example is because the target database does not existEng = Create_engine (dirname (DSN), echo = True)#DirName Returns the directory of the DSN. is actually the first element of Os.path.split (path). The effect is equivalent to removing the database from DSNEng.execute ('Create DATABASE%s'%DBNAME). Close () printf ('New Database%s succeeded'%DBNAME) eng.create_engine (Dsn,echo=True) printf ('connection to database%s succeeded'%DBNAME)#After you create a database engine connection, you need to create a Session object sessions, which is actually a factory.         #Sessionmaker () can also be created before Create_engine, and then wait until the database engine connection is created, calling Session.configure (bind=) to implement the bindingSession = Orm.sessionmaker (bind=Eng)#Session () You can create an object that is bound to a database. But so far, it hasn't opened any connections.         #when it is first invoked, it attempts to retrieve a link from the Database Engine connection pool, which is held until all tasks are committed or the session object is closedSelf.ses =Session ()#Save the user's table and database engine as attributes of the instance. This means that all operations on this table will be bound to the specified engine. Self.users = users.__table__Self.eng= Self.users.metadata.bind = Eng#The additional binding of the engine and the table metadata means that all operations on this table are bound to the specified engine    defInsert (self): Self.ses.add_all (Users login= Who,userid=userid,projid=randrange (1,5)) forWho,useridinchrandname ()) Self.ses.commit ()defUpdate (self): FR= Randrange (1,5) to= Randrange (1,5) I=-1Users= Self.ses.query (Users). filter_by (ProjId =fr). All () forI, userinchEnumerate (users): User.projid=To Self.ses.commit () printf ('\ nthe%s user name changed from%s to%s'% (i+1, Fr,to)) defDelete (self): RM= Randrange (1,5) I=-1Users= Self.ses.query (Users). filter_by (ProjId =RM). All () forI,userinchEnumerate (users): Self.ses.delete (user) Self.ses.commit () printf ('A total of%s users with group number%s were deleted'% (rm,i+1))        defFinish (self): Self.ses.connection (). Close ()defDbDump (self): printf ('\n%s'%"'. Join (Map (cformat,fields))) Users=self.ses.query (Users). All () forUserinchusers:printf (user) Self.ses.commit ()def __getattr__(self,attr):#the drop and create methods actually only need to invoke the drop () and create () methods of the table, without having to write them individually. The delegate mechanism is used here-the __getattr__ () method is called when the property lookup fails.         returnGetAttr (self.users,attr)#use Help (getattr) for assistance. GetAttr () Gets the properties of the object, GetAttr (x, ' Y ') is equivalent to performing x.y.     defMain (): printf ('connecting to the%s database'%DBNAME) DB=Setup ()ifDb not inchdsns:printf ('\ error:%s database is not supported, program exits')        return            Try: Orm=sqlalchemytest (dsns[db])exceptruntimeerror:printf ('\nerror:%s database is not supported, program exits') printf ('\n*** Creating the Users table ()') Orm.drop (Checkfirst= True)#orm.create () printf ('\n*** Create users table, Success!!! ') printf ('%%% inserting Data') Orm.insert () orm.dbdump () printf ('\ nthe user is randomly grouped') orm.update () orm.dbdump () printf ('\ n! Randomly delete users') Orm.delete () orm.dbdump () printf ('\n*** Delete a table') Orm.drop () printf ('\ nthe database connection is closed!') orm.finish ()if __name__=='__main__': Main ()

If you have a warning (and do not affect the results of the program running) during the run, you can set the following:

Import warningswarnings.filterwarnings ("ignore") # Ignore warnings

Python connects to the database using SQLAlchemy