NO1:
"SQLite"
Insert
#Import SQLite driver:>>>ImportSqlite3#Connect to the SQLite database#The database file is test.db#If the file does not exist, it is automatically created in the current directory:>>> conn = Sqlite3.connect ('test.db')#Create a cursor:>>> cursor =conn.cursor ()#execute an SQL statement to create the user table:>>> Cursor.execute ('CREATE TABLE User (ID varchar () primary key, name varchar )')<sqlite3. Cursor Object at 0x10f8aa260>#continue executing an SQL statement and insert a record:>>> Cursor.execute ('INSERT into user (ID, name) VALUES (\ ' 1\ ', \ ' michael\ ')')<sqlite3. Cursor Object at 0x10f8aa260>#to get the number of rows inserted through ROWCOUNT:>>>Cursor.rowcount1#Close cursor:>>>cursor.close ()#COMMIT TRANSACTION:>>>Conn.commit ()#Close connection:>>> Conn.close ()
Inquire
>>> conn = Sqlite3.connect ('test.db')>>> cursor =conn.cursor ()#To execute a query statement:>>> Cursor.execute ('SELECT * from user where id=?', ('1',))<sqlite3. Cursor Object at 0x10f8aa340>#To obtain a query result set:>>> values =Cursor.fetchall ()>>>values[('1','Michael')]>>>cursor.close ()>>> Conn.close ()
No2:
"MySQL"
Install MySQL Driver
$ pip Install Mysql-connector-python--allow-external Mysql-connector-python
If the above command fails to install, you can try another driver:
$ pip Install Mysql-connector
#import MySQL driver:>>>ImportMysql.connector#be careful to set password as your root password:>>> conn = Mysql.connector.connect (user='Root', password='Password', database='Test')>>> cursor =conn.cursor ()#To create a user table:>>> Cursor.execute ('CREATE TABLE User (ID varchar () primary key, name varchar )')#insert a row of records, note that the MySQL placeholder is%s:>>> Cursor.execute ('INSERT into user (ID, name) values (%s,%s)', ['1','Michael'])>>>Cursor.rowcount1#COMMIT TRANSACTION:>>>Conn.commit ()>>>cursor.close ()#To run the query:>>> cursor =conn.cursor ()>>> Cursor.execute ('SELECT * from user where id =%s', ('1',))>>> values =Cursor.fetchall ()>>>values[('1','Michael')]#close the cursor and connection:>>>cursor.close () True>>> Conn.close ()
No3:
"SQLAlchemy" ORM Framework
Installation
$ pip Install SQLAlchemy
and initialize the dbsession:
#Import: fromSQLAlchemyImportColumn, String, Create_engine fromSqlalchemy.ormImportSessionmaker fromSqlalchemy.ext.declarativeImportDeclarative_base#to create a base class for an object:Base =declarative_base ()#To define a user object:classUser (Base):#Name of the table: __tablename__='User' #Structure of the table:id = Column (String), primary_key=True) name= Column (String (20))#To initialize a database connection:Engine = Create_engine ('Mysql+mysqlconnector://root:[email protected]:3306/test')#To Create a dbsession type:Dbsession = Sessionmaker (bind=engine)
adding objects
# Create Session object:session = dbsession ()# Create a new User object:new_user = User (id='5 ', name='Bob')# added to session: Session.add (New_user)# commit is saved to database:session.commit ()# Close session:Session.close ()
Inquire
#To create a session:Session =dbsession ()#Create a query, filter is the Where condition, and the last Call to one () returns a unique row, and all rows are returned if all () is called:user = Session.query (user). Filter (user.id=='5'). One ()#print type and object's Name property:Print('Type:', type (user))Print('Name:', User.Name)#Close session:Session.close ()
"Python" Database