Preface:
Hello everyone, today I'm here to talk to you about theSQLALchemyThis module, the module is the most famous ORM framework in Python, the framework is built on top of the database API, using relational object mapping for database operations, in short: the object will be converted to SQL, and then use the data API to execute SQL and get the results.
See here, I believe that many readers may feel confused, we will pass a simple case in the illustration of it.
For example, if we want to create a new table in mysql, we first need to connect to the database.The code is as follows:
：# Connect to the database sql_connect = 'mysql+pymysql://root:123456@localhost:3306/sql_prac?charset=utf8' engine = create_engine(sql_connect) DBSession = sessionmaker(bind=engine) # Base class for object creation. BaseModel = declarative_base()
I. Definition table structure
For the newly created table, we name it "User" and we need to define the table structure.
The code is as follows:
#define objects class User(BaseModel): # Table name __tablename__ = 'user' # Table structure where ID is set as the primary key and is auto-incremented id = Column(Integer, primary_key=True, autoincrement=True) name = Column(String(20)) age = Column(Integer)
II. Creating and deleting tables
For table creation and table deletion operations, the code is as follows:
# Create mapped database tables def init_db(): .create_all(engine) # Delete mapped database tables def drop_db(): .drop_all(engine)
III. Insertion of data
We can try to insert a few values into the new table.
The code is as follows:
def insert_data(name_1, age_1): # Create a session object, equivalent to a cursor in MySQL db. session = DBSession() # Create a new User object. new_user = User(name=name_1, age=age_1) # Add to session. (new_user) # Submit operations to add data () # Close session () if __name__ == "__main__": insert_data(name_1="Mike", age_1=20) insert_data(name_1="John", age_1=35) .......
The result after running is shown below:
IV. Queries
If we want to query the data in the table, we can do it like this
# Create Session. session = DBSession() # Create a Query query where filter is the where condition and the last call to one() returns the only row, or all rows if all() is called:. user = (User).filter( == 'Tom').one() # Print the name attribute and the age attribute of types and objects. print(, ) # Close Session. ()
If the call is all()then all rows are returned, so we need to iterate through the results in a for loop and print them.
The code is as follows:
users = (User).filter( == 'John').all() for u in users: print(, )
V. Updating and deleting data
Let's try to update some data in the table with the following code:
# Create Session.
session = DBSession()
# Multiple data updates are possible
user = (User).filter( == 3)
({: 30})
# Submission of data
()
# Close Session
()By "ID" to lock the location of the data to be updated, and then we call the update () method to change its age to the specified value. At the same time, we can also delete some of the values in the table.
The code is as follows:
# Create Session session = DBSession() # What data is deleted user = (User).filter( == 5).one() (user) # Submission of data () # Close session ()
Again, we target the location of the data to be deleted by its "ID" and call thedelete()Methods.
VI. Run the SQL statement directly
Of course we are creating thesessionAfter that, we can also run SQL statements directly in there, for example if we want to see what databases there are in total.
The code is as follows:
session = DBSession()
print(('show databases').fetchall())
()Or we are trying to return all the data in the table, the code is as follows:
session = DBSession()
print(('select * from user').fetchall())
()VII. DataFrame to MySQL database
We can also batch excel or csv files in the data batch imported into the MySQL database, we first read the data in the file through Pandas.
The code is as follows:
sql_connect = 'mysql+pymysql://username:password@ip address:port number/database name?charset=utf8'
engine = create_engine(sql_connect)
df = pd.read_excel("sqlalchemy_test1.xlsx")
df.to_sql("user", engine, index=False, if_exists='append')Of course, we can also read data from a table in the database, the code is as follows:
df = pd.read_sql("Form name", engine)
print(())To this point, this article on the sharing of a Python encounter database module is a good use of the article is introduced to this, more related Python module content, please search for my previous articles or continue to browse the following related articles I hope you will support me in the future more!