MySQL > SQLAIchemy > SQLAIchemy > SQLAIchemy > SQLAIchemy

Hello, I’m Yue Chuang.

First, Python installation stuff

Python 1.1 installation

Download method

Visit the official website: www.python.org

As shown in figure:

1. Select the Overhead Downloads option
2. Select your system in the popup option box (note: if you directly click the gray button on the right, you will download 32 bits)

Enter the download page, as shown in the figure:

1. Download for 64-bit files
2. Download for 32-bit files

Select your corresponding file download.

Installation precautions:

(Photo from Internet)

Customizing options to choose where to store files, etc., makes Python more user-friendly.

Default installation: all the way Next to the end, installation is more convenient and faster.

Special note: make sure to check the arrow in the picture. Otherwise, you have to manually configure environment variables.

Q: How do you configure environment variables?

A: Control Panel – System and Security – System – Advanced System Settings – Environment Variables – System Variables – Double-click Path – Go to the edit environment variables window and fill in the blank space where Python is located. – Ok.

check

After installing Python, Win+R opens the run window and enters CMD to enter the command line mode and enter Python. If the Python version number and other commands are shown in the following figure, Python is successfully installed.

1.2 The Python compiler Sublime

Website: www.sublimetext.com/

Reasons for choosing this editor:

1. Don’t need too much programming foundation, quick start
2. Fast startup speed
3. The key reason — free

Q&A

You cannot run the command using the shortcut key Ctrl+B. You can try Ctrl+Shift+P and select Bulid With: Python in the displayed window.

Or select the Build With option in the Tool option at the top, and select Python in the window that pops up.

Quick introduction to Python

Although this section is zero foundation friendly article, but also has the depth of some knowledge points to expand, programming foundation can also selectively watch oh!

2.1 Python Interactive mode and Command line Mode

2.1.1 Cli Mode

1. Access mode

Windows:

1. Click Start, run, CMD enter
2. Press WIN+R, CMD enter

Mac:

1. Open Launchpad in the Apps menu, find and open the other folder, and click Terminals.
2. Open a Finder window and search for the terminal keyword directly in the Applications directory

2. Prompt

The command prompt varies depending on the operating system. For example, in Windows, the command prompt is as follows:

C:\ machine name \ username >Copy the code

2.1.2 Interactive mode

1. Access mode

Enter by typing a Python command in command mode, and exit() to exit interactive mode.

2. Prompt

>>>
Copy the code

The difference between

1. The py file can only be run on the command line;
2. Python interactive mode code is input one line, execute one line; Running the.py file directly on the command line executes all the code in the file at once.

From this point of view, Python interaction mode is primarily used to debug code.

2.2 Data types and variables

The main data types in Python are: int, float, bool, string, list, tuple, dict, and set.

  2                 # integer (int)
  3.1314526         # float
  True              # Boolean value (bool)
  "1"               # string (STR)
  [1.2."a"]         # list (list)
  (1.2."a")         # tuples (a tuple)
  {"name":"Xiao Ming"}   Dict (# dictionary)
Copy the code

In Python, you can use # to comment information that the IDE ignores automatically when compiling.

2.2.1 integer

In line with the concept of integers in mathematics, there are four base representations: binary, octal, decimal and hexadecimal. By default, integers are in decimal.

(Photo from Internet)

2.2.2 floating point number

Represents a number with a decimal point. Floating-point numbers are represented in two ways: decimal and scientific notation. (Note: calculators or computers express the power of 10 using E or E, i.e. 2.88714E13=28871400000000)

2.2.3 Boolean value

Boolean values in Python have two quantities: True and False, which are 1 and 0 respectively (True and False are case-sensitive).

The code is as follows:

 var1 = 12
 var2 = 12
 var3 = 13
 print(var1==var2) # output True
 print(var1==var3) # output False
Copy the code

Var1 ==var2 == = is the comparator, compares whether var1 is equal to var2, if True, otherwise False.

In addition, Boolean values can be computed with and, OR, and not.

The code is as follows:

And computing: unflinching, demanding allTrueOtherwise, the output isFalse.True and True #True
True and False #False
False and False #False
Copy the code

The code is as follows:

Or operation: requirements are not high, as long as there is aTrueThe output is zeroTrue.True or True #True
True or False #True
False or False #False
Copy the code

The code is as follows:

Non - operation: always a naysayer, inputTrueIt gives you outputFalseAnd vice versa. (Special note: it is a monocular operator)not True #False 
not False #True
Copy the code

2.2.4 string

A string is any text enclosed in single or double quotation marks, such as ‘aaa’ and ‘ABC’. ” or “” are just representations themselves, not part of the string, so the string ‘aaa’ has only three characters, aaa.

What if the string already contains’ or “? We can identify it with the escape character \, as in:

The you’re string is represented as:

"you\' re"
Copy the code

What if the string content contains’ also contains \? Then we can use \\ as the code:

"you\\'re"
Copy the code

2.2.5 list

Lists are one of the more important data containers in Python.

The code is as follows:

 list1 = [1.2.3.4.5]
 list2 = ["AI yue chong"."GitChat"."Fly"]
Copy the code

Lists are indexed, so to access values in a list, you simply need the list name + the index value.

print(list1[2])  Output: 3
print(list2[0])  Output: AI Yue Chuang
Example # 2
lists = ['a'.'b'.'c']
lists.append('d')
print(lists)
print(len(lists))
lists.insert(0.'mm')
lists.pop()Delete the last element
print(lists)
# output
['a'.'b'.'c'.'d']
4
['mm'.'a'.'b'.'c']
Copy the code

2.2.6 tuples

Tuple creation is as simple as adding elements in parentheses, separated by commas.

Example code:

tup1=('aaa'.1.'bbb'.2)
Copy the code

Note: When a group contains only one element, you need to add a comma after the element, otherwise the parentheses will be used as operators.

>>> tup1=(1)  
>>> type(tup1)
<class 'int'> > > >tup2= (1.) > > >type(tup2)
<class 'tuple'>
Copy the code

The difference between lists and tuples

I don’t know if you’ll notice any similarities between lists and tuples, but the main differences are:

1. Tuples use parentheses and lists use square brackets.
2. Lists are dynamic, their length is not fixed, and you can add, subtract, or change elements at will (mutable).
3. Tuples are static and cannot be added, subtracted or changed (immutable).

In fact, ** is the most important difference between lists and tuples, and this difference will affect how they are stored. We can look at the following example:

l = [1.2.3]
l.__sizeof__()
64
tup = (1.2.3)
tup.__sizeof__()
48
Copy the code

You can see that for lists and tuples, we put the same element, but the tuple has 16 bytes less storage space than the list. Why is that?

In fact, since the list is dynamic, it needs to store Pointers to the corresponding elements (in the case of ints, 8 bytes). In addition, because the list is mutable, you need to store the size of the allocated length (8 bytes) so that you can track the list space usage in real time and allocate additional space when you run out of space.

Example code:

L = [] L.__sizeof__ () // The storage space of the empty list is40byte40
l.append(1)
l.__sizeof__() 
72// Add an element1After that, the list allocates it for storage4The space of two elements (72 - 40) /8 = 4
l.append(2) 
l.__sizeof__()
72// Add elements because space was allocated earlier2, list space unchanged L.append (3)
l.__sizeof__() 
72/ / same as above L.A. ppend (4)
l.__sizeof__() 
72/ / same as above L.A. ppend (5)
l.__sizeof__() 
104// Add elements5Later, the list ran out of space, so extra storage was allocated4The space of one elementCopy the code

The above example Outlines the process of list space allocation. We can see that, in order to reduce the overhead of allocating space per add/subtract, Python allocated more and more space each time, such mechanism (over-allocating) ensured the efficiency of its operation: the time complexity of adding/removing was O(1).

But for tuples, it’s a different story. The length is fixed and the elements are immutable, so the storage space is fixed.

Looking at the previous analysis, you might think that such differences are negligible. But imagine if lists and tuples stored 100 million, billion or more elements. Could you ignore the difference?

So we can conclude that tuples are lighter than lists, so overall, tuples perform slightly faster than lists.

2.2.7 dictionary

A dictionary is a special kind of list in which each pair of elements is divided into keys and values. Add, delete, change and check values are accomplished by keys. Note: The build /KEY in the dictionary must be immutable data types, such as int, float, string, and tuple.

The code is as follows:

 brands = {"Tencent":"Tencent"."Baidu":"Baidu"."Alibaba":"Alibaba"}

 brands["Tencent"]  Get value with key value "Tencent"
 del brands["Tencent"] # Delete Tencent
 brands.values[] # get all values
 brands.get("Tencent")  Get value with key value "Tencent"
Copy the code

2.2.8 collection

A set is an unordered sequence of non-repeating elements that can be created by {} or set().

The code is as follows:

set1={'a'.'aa'.'aaa'.'aaaa'} #{'aaa', 'aa', 'aaaa', 'a'}
set1=set(['a'.'aa'.'aaa'.'aaaa'])
print(set1)  #{'aaaa', 'aa', 'a', 'aaa'}
Copy the code

Note: To create an empty collection, you must use set() instead of {}, because {} is used to create an empty dictionary.

>>> s={}
>>> type(s)
<class 'dict'>
Copy the code

expand

In the context of data types, we talk a lot about mutable objects, immutable objects, but what is mutable and what is immutable?

Here’s a hint:

• Python immutable objects: int, float, tuple, string
• Python mutable objects: list, dict, set

A mutable object is an object whose elements can be changed. An immutable object is an object whose elements cannot be changed. The difference between the two is that its elements can be changed.

In what way do we usually try to modify objects? As the saying goes, “things are not much, but in common”, here, we introduce “add, delete, change and check” these common methods.

Take the mutable object list as an example, add append and INSERT.

The code is as follows:

>>> list= ["a"."b"]
>>> list.append("c") # append(element) to add the element to the list
>>> print(list)
['a'.'b'.'c']

>>> list.insert(0."d")#insert(index, element) to add the element to the specified position
>>> print(list)
['d'.'a'.'b'.'c']
Copy the code

Delete: remove(), pop(index), pop()

Run the following code:

>>> list.remove("d")#remove objectionable elements from the list
>>> list
['a'.'b'.'c']
>>> list.pop(1)
'b'# Deleted element
>>> print(list)
['a'.'c']#pop(index), remove the element specifying the position
>>> list.pop()
'c'# Deleted element
>>> print(list)#pop(), deletes the last element by default
['a']
Copy the code

Modified: list [index] = element

The code is as follows:

>>> list= ['a'.'c']
>>> list[0] ='b'# Replace the element in the specified position
>>> print(list)
['b'.'c']
Copy the code

Find: list [index]

The code is as follows:

>>> list= ['b'.'c']
>>> list[1]Find the element at the specified position
'c'
Copy the code

All mutable object lists are modified successfully. Now let’s try a simple change to the immutable tuple and see what happens.

>>> tuple1=(1.2.3.4)
>>> tuple1[0] =5
Traceback (most recent call last):
  File "<stdin>", line 1.in <module>
TypeError: 'tuple' object does not support item assignment# error
Copy the code

Through the above changes to the list and tuple, we can confirm one of the most straightforward differences between immutable objects and mutable objects: mutable objects can be modified, while immutable objects cannot be modified.

2.2.9 variable

When we type 1+1 in the CMD console, the console outputs 2. But what if we want to continue to use this 2 in future calculations? We need a “variable” to store the value we need.

The code is as follows:

a=1+1  A is a variable used to store the 2 produced by 1+1
Copy the code

As shown in the chestnut above: Variable assignment in Python does not require a type declaration.

Tip: creating a variable creates a space in memory. Based on the variable’s data type (default to integer if no data type is specified), the interpreter allocates specified memory and decides what data can be stored in memory.

expand

Isn’t that amazing? B =a If A has changed, b should have changed with it.

Let’s make a hypothesis:

Assuming that developer = memory, variable = house, and variable stored value = households, before B = A, the general trend of A =1 makes developers build house A well. When B = A is copied, the developer draws a block of memory to build house B without stopping, and both house B and House A live with the value 1. So when a=4, house A has a new inhabitant, but it doesn’t affect the inhabitant of house B — the inhabitant of number 1.

2.3 Conditions, loops, and other statements

Python uses if and else as conditional statements.

Example code:

# If... The else...
i = 1
if i == 1:
    print("Yes,it is 1")
else:
    print("No,it is not 1")
# if... The else... Is a classic judgment statement. Note that there is a colon after the if expression and also a colon after the else.
Copy the code

The above statement determines whether the variable I is equal to 1. Note that Python takes indentation seriously. So when you write a statement, you need to pay attention to whether the indentation is in the same area.

Python supports for loops and while loops. Loop statements are similar to if and else statements, such as colon and indentation.

for i in range(1.10) :print(i)
Copy the code

The above statement prints numbers between 1 and 10. Note that range(1,10) ranges from 1 to 9 and does not include 10.

i = 1
while (i<10):
    i += 1
    ifi! =8:
          continue
    else:
          break
Copy the code

In the above statement, the word break means: to break out of the loop; Continue means: to continue the cycle.

2.3.1 function

We normally use print() and type(), both of which are functions. For repetitive code segments, we don’t need to write them out every time, just call them by the name of the function.

The keyword for defining a function is def, defined in much the same way as for loops.

The code is as follows:

def function(param) :  # function = function, param = parameter
    i = 1
    return i  # f returns the valueAnd just to make it a little bit more graphic, let's write a sum of a plus b.def getsum(a,b) :  GetSum (a,b)
    sum = a+b;
    return sum;  # return the sum of a+b, sum

print(getsum(1.2))
Copy the code

Once defined, we can use this function elsewhere in the program by calling getSum(a,b).

2.3.2 file

Python provides rich and easy-to-use file manipulation functions, so let’s take a quick look at common operations.

open()

The code is as follows:

open("abc.txt"."r")  
#open() is Python's built-in file function for opening files, "abc.txt" for the target file name, "r" for open files in read-only mode, and other "W" and "A" modes
Copy the code

read()

Open files that must be retrieved using the.read() method.

file = open("abc.txt"."r")
words = file.read()
Copy the code

For detailed reading, you can refer to this article: With Open ~, a collection of basic file operations in Python.

Relational database introduction

3.1 Entering a Topic

3.1.1 MySQL and the Flask

MySQL

MySQL is one of the most popular relational database management systems. In Web applications, MySQL is one of the best RDBMS (relational database management system) applications.

Flask

Flask, a major force in Python Web development. Database development using SQLAlchemy. The use of ORM is the trend.

3.1.2 MySQL

A Database is a warehouse that organizes, stores, and manages data according to data structures.

Each database has one or more different apis for creating, accessing, managing, searching, and copying saved databases. CURD.

Three paradigms of databases

paradigm	content
The first paradigm	The first paradigm is the most basic paradigm. If all field values in a database table areNondecomposable atomic value.
	This indicates that the database table satisfies the first normal form.
The second paradigm	The second normal form is required to ensure that the database tables inEach column is associated with the primary keyIt can’t be associated with just one part of the primary key
	(mainly for union primary keys). This means that only one type of data can be stored in a database table,
	You cannot keep multiple data in the same database table.
The third paradigm	The third normal form needs to ensure that every column in the data table is equalPrimary keys are directly related, but not indirectly.

3.2 database

Relational database is a database built on the basis of relational model, with the help of set, algebra, and other mathematical concepts and methods to deal with the data in the database.

Features:

1. The data is presented as a table
2. Each line/is a variety of record names
3. Each column/is the data field corresponding to the record name
4. Many rows and columns make up a form
5. Several forms form the Database

3.2.1 MySQL installation

www.runoob.com/mysql/mysql…

To check whether the installation is successful, run the following program command:

mysql -u root -p
Enter the password
Copy the code

Let’s create a database for the rest of our study:

create database test;
Query OK, 1 row affected (0.00 SEC)
ERROR 1007 (HY000): Can't create database 'test'; Database exists Indicates that the database already exists


# check whether our database was created successfully:
show databases;
Mysql > create database (test);

# Let's use the database simply:
use test;
# use The name of the database to use;
# Database changed will be returned to you after you type

We can use the following command to view the database contents
show tables;
# it will return you:
Empty set (0.01 sec)
Copy the code

3.3 SQLAIchemy Operates MySQL

3.3.1 Introduction to SQL Alchemy

SQLAlchemy is an ORM framework in the Python programming language that builds on the database API and uses relational object mapping to perform database operations. In short, it converts objects into SQL and then executes SQL using the data API and gets the results.

3.3.2 ORM methodology is based on three core principles

The name of the	role
simple	Model the data in its most basic form
Communicate sexual	The database structure is documented in a language that anyone can understand
accuracy	Create correctly standardized structures based on the data model

Install the library:

pip install sqlalchemy pymysql
# sqlAlchemy: is our ORM
# pymysql: is the equivalent of a gateway when we can perform this SQLalchemy
Copy the code

Call different database apis according to different configuration files, so as to achieve operations on the database, such as:

'database type + database driver name: / / user name: password @ machine address: port/database name' mysql + pymysql: / / < username > : < password > @ < host > | < dbname > [? < options >] mysql+mysqldb://<user>:<password>@<host>[:<port>]/<dbname>Copy the code

mysql+pymysql://:@/[?]
# mysql+pymysql://<username>:<password>@<host>|<dbname>[?<options>]

mysql+mysqldb://:@[:]/
# mysql+mysqldb://<user>:<password>@<host>[:<port>]/<dbname>
Copy the code

Version 2.7 uses MySQLdb version 3.5 uses PyMySQL.

1. Connect to the database

Mysql > create database test (‘ test ‘);

from sqlalchemy import create_engine

engine = create_engine(
	"Mysql + pymysql: / / root: 123456 @127.0.0.1:3306 / test".Mysql +pymysql do not add Spaces between mysql and pymysql
	# "mysql + pymysql://root:root@localhost/test",
	max_overflow = 5.# The maximum number of external links that can be created after the connection pool size is exceeded
	pool_size = 10.# connection pool size
	echo = True.Display debugging information
)
Copy the code

After writing the above code, we need to run the program to see if the program reports an error. If there is no error, the code has no problem. If you get an error, carefully compare your code with mine and write it according to your actual database.

PS: The above code is boilerplate code, we connect the database is to use the above code.

2. Basic knowledge

SQL Alchemy	Python
Text	Long str
Boolean	bool
BigInteger	int
Date	Datetime.data
DateTime	Datetime.datetime
Float	float
String	str

Import the DateTime field, default passes in the function, not the result, and no parentheses are required.

If we want to create a Table, we need to import a Table.

from sqlalchemy import Table
Of course, we need something else, such as Column
from sqlalchemy import Column
We import the Table and Column from the same library, so we can write this as:
from sqlalchemy import Table,Column
Next I'll import the library functions we need to use:
from aqlalchemy import Table,Column,String,Integer,Boolean,MetaData
from datetime import datetime
Copy the code

Parsing code

metadata = MetaData() Get metadata, introduce the database
This metadata is equivalent to your description of the database, or you can write nothing. Sqlalchemy import MetaData to sqlAlchemy.
# Column: Column
# default: default
test=Table('Data name', metadata, Column('Field name'.'Field type'For a description of the field, for example: the first primary key, write (primary_key=True), so is automatic growthTrue(autoincrement=True(That is to say,This id is used as the primary key and is automatically incremented as the contents of the table increase.
          Column('name',String(255)),
           # Then, the first field is our name (name), up to 255 bytes long
          Column('data',DateTime(),default=datetime.now,onupdate=datetime.now),
           Datetime(); default=datetime.now()
          Column('main',Boolean(),default=False),
          # is not male, type is Boolean, default=False
          )

Copy the code

The original code

from aqlalchemy import Table,Column,String,Integer,Boolean,MetaData
from datetime import datetime

metadata = MetaData() Get metadata, introduce the database
This metadata is equivalent to your description of the database, or you can write nothing. Sqlalchemy import MetaData to sqlAlchemy.
test=Table('test',metadata,
          Column('id',Integer(),primary_key=True,autoincrement=True),
          Column('name',String(255)),
          Column('data',DateTime(),default=datetime.now,onupdate=datetime.now),
          Column('main',Boolean(),default=False),Copy the code

At this time impatient friends will directly run the code, and then there will be a question directly to ask the teacher ME. The teacher zha this also did not create ah, why? Because I still need to tell you one point of knowledge, one thing. Of course, this will be covered later, I will first add a line of code to the above code to execute the following:

metadata.create_all(engine) Create table
Copy the code

If it works, it prints a bunch of messages:

"C:\Program Files\Python37\python.exe"D:/daima/ pyCharm_daima /07- Relational database/create_databases_table.py2020- 01 -11 16:27:40.335 INFO sqlalchemy.engine.base.Engine SHOW VARIABLES LIKE 'sql_mode'
2020- 01 -11 16:27:40.335 INFO sqlalchemy.engine.base.Engine {}
2020- 01 -11 16:27:40.343 INFO sqlalchemy.engine.base.Engine SHOW VARIABLES LIKE 'lower_case_table_names'
2020- 01 -11 16:27:40.344 INFO sqlalchemy.engine.base.Engine {}
C:\Program Files\Python37\lib\site-packages\pymysql\cursors.py:170: Warning: (1366."Incorrect string value: '\\xBB\\xC6\\xBC\\xD2\\xB1\\xA6... ' for column 'VARIABLE_VALUE' at row 75")
  result = self._query(query)
C:\Program Files\Python37\lib\site-packages\pymysql\cursors.py:170: Warning: (1366."Incorrect string value: '\\xBB\\xC6\\xBC\\xD2\\xB1\\xA6' for column 'VARIABLE_VALUE' at row 94")
  result = self._query(query)
C:\Program Files\Python37\lib\site-packages\pymysql\cursors.py:170: Warning: (1366."Incorrect string value: '\\xBB\\xC6\\xBC\\xD2\\xB1\\xA6... ' for column 'VARIABLE_VALUE' at row 259")
  result = self._query(query)
C:\Program Files\Python37\lib\site-packages\pymysql\cursors.py:170: Warning: (1366."Incorrect string value: '\\xBB\\xC6\\xBC\\xD2\\xB1\\xA6... ' for column 'VARIABLE_VALUE' at row 377")
  result = self._query(query)
C:\Program Files\Python37\lib\site-packages\pymysql\cursors.py:170: Warning: (1366."Incorrect string value: '\\xBB\\xC6\\xBC\\xD2\\xB1\\xA6... ' for column 'VARIABLE_VALUE' at row 403")
  result = self._query(query)
C:\Program Files\Python37\lib\site-packages\pymysql\cursors.py:170: Warning: (1366."Incorrect string value: '\\xBB\\xC6\\xBC\\xD2\\xB1\\xA6... ' for column 'VARIABLE_VALUE' at row 404")
  result = self._query(query)
C:\Program Files\Python37\lib\site-packages\pymysql\cursors.py:170: Warning: (1366."Incorrect string value: '\\xBB\\xC6\\xBC\\xD2\\xB1\\xA6... ' for column 'VARIABLE_VALUE' at row 455")
  result = self._query(query)
C:\Program Files\Python37\lib\site-packages\pymysql\cursors.py:170: Warning: (1366."Incorrect string value: '\\xD6\\xD0\\xB9\\xFA\\xB1\\xEA... ' for column 'VARIABLE_VALUE' at row 484")
  result = self._query(query)
2020- 01 -11 16:27:40.349 INFO sqlalchemy.engine.base.Engine SELECT DATABASE()
2020- 01 -11 16:27:40.349 INFO sqlalchemy.engine.base.Engine {}
2020- 01 -11 16:27:40.350 INFO sqlalchemy.engine.base.Engine show collation where `Charset` = 'utf8mb4' and `Collation` = 'utf8mb4_bin'
2020- 01 -11 16:27:40.350 INFO sqlalchemy.engine.base.Engine {}
2020- 01 -11 16:27:40.352 INFO sqlalchemy.engine.base.Engine SELECT CAST('test plain returns' AS CHAR(60)) AS anon_1
2020- 01 -11 16:27:40.352 INFO sqlalchemy.engine.base.Engine {}
2020- 01 -11 16:27:40.354 INFO sqlalchemy.engine.base.Engine SELECT CAST('test unicode returns' AS CHAR(60)) AS anon_1
2020- 01 -11 16:27:40.354 INFO sqlalchemy.engine.base.Engine {}
2020- 01 -11 16:27:40.355 INFO sqlalchemy.engine.base.Engine SELECT CAST('test collated returns' AS CHAR CHARACTER SET utf8mb4) COLLATE utf8mb4_bin AS anon_1
2020- 01 -11 16:27:40.355 INFO sqlalchemy.engine.base.Engine {}
2020- 01 -11 16:27:40.358 INFO sqlalchemy.engine.base.Engine DESCRIBE `test`
2020- 01 -11 16:27:40.358 INFO sqlalchemy.engine.base.Engine {}
2020- 01 -11 16:27:40.359 INFO sqlalchemy.engine.base.Engine ROLLBACK
2020- 01 -11 16:27:40.360 INFO sqlalchemy.engine.base.Engine 
CREATE TABLE test (
	id INTEGER NOT NULL AUTO_INCREMENT, 
	name VARCHAR(255), 
	date DATETIME, 
	man BOOL, 
	PRIMARY KEY (id), 
	CHECK (man IN (0.1)))2020- 01 -11 16:27:40.360 INFO sqlalchemy.engine.base.Engine {}
2020- 01 -11 16:27:40.407 INFO sqlalchemy.engine.base.Engine COMMIT

Process finished with exit code 0

Copy the code

Extension: The original statement has been converted to the MySQL native statement after using Python operations:

Sqlalchemy sqlalchemy
test=Table('test',metadata,
          Column('id',Integer(),primary_key=True,autoincrement=True),
          Column('name',String(255)),
          Column('data',DateTime(),default=datetime.now,onupdate=datetime.now),
          Column('main',Boolean(),default=False),# MySQL native statement:
CREATE TABLE test (
	id INTEGER NOT NULL AUTO_INCREMENT, 
	name VARCHAR(255), 
	date DATETIME, 
	man BOOL, 
	PRIMARY KEY (id), 
	CHECK (man IN (0.1)))Copy the code

Question from a student:

Cadet: Do I have to put parentheses on this?

I: default is passed as a function, not the result of execution, no parentheses required.

Me: This callback will be executed internally

Student: What’s the difference between parentheses and no parentheses?

Me: Default is a function that is called with parentheses and becomes a value.

Student: Not really

Me: Default needs a function, which is executed by adding ()

Student: So if you only need one function you don’t add () and if you need parentheses you just run it?

Me: The execution function returns a value, and passing default after () is not a function, but the execution result of datetime.now().

Me: The purpose of this function passed in is to call itself, the program will call the function passed in when using this function, called the callback function. (He will call it himself)

Ok, we continue with the extension: after running the code (that is, after creating our table), we can type in the command (that is, MySQL has been started) :

If you already use MySQL, you can enter the following command:
desc test;
# Of course, if you don't understand, please close the console and let's start from scratch and check the database table we just created
（1Mysql -u root -p123456 mysql -u root -p123456 mysql -u root -p1234562) use the test; (3) desc test;Copy the code

Operation diagram:

It’s Python code, but our ORM will do it for me.

The name of the	role
MetaData()	Get metadata and introduce the database

3. Things

In general, things must satisfy four conditions (ACID)

• atomic
• consistency
• Isolation,
• persistence

Transactions: Transactions are sessions where we don’t perform an operation ** that is, I go to the ATM to insert my card and deposit money until the card is returned. This is a session.

Next, I use the example of a hundred dollars in the bank:

• Atomicity: Everything we do is an insertion of data, a small event.
• Consistency: Things like when we put money in or take money out, or when there is a change in the middle, can be completely displayed on my chart. (No, I put in a hundred dollars and the table doesn’t change at all.) In other words, the changes are consistent.
• Isolation: for example, two different people, I save a hundred and you save a hundred are two different things, I withdraw money will not affect the change of your assets, this is what we call isolation.
• Persistence: Persistence means that our data will always be there, and it won’t change if you don’t do anything about it.

BEGIN, ROLLBACK, COMMIT

BEGIN	Start something
ROLLBACK	Things rolled back
COMMIT	Confirmation of transaction (submission)

Above we create a table, and then we get a connection with: conn=engine.connect(). Next we start something: start=conn.begin().

Extension: Improves the security of database connections by using nested functions.

Connect to the database
from sqlalchemy import create_engine,MetaData,Table


def  my_dabase_big() :
	def my_database() :
		engine = create_engine(
			"Mysql + pymysql: / / root: 123456 @127.0.0.1:3306 / test".Mysql +pymysql do not add Spaces between mysql and pymysql
			# "mysql + pymysql://root:root@localhost/test",
			max_overflow = 5.# The maximum number of external links that can be created after the connection pool size is exceeded
			pool_size = 10.# connection pool size
			echo = True.Display debugging information
		)
	my_database()
my_dabase_big()
Copy the code

Connect to the database
from sqlalchemy import create_engine,MetaData,Table


def  my_dabase_big() :
	def my_database() :
		engine = create_engine(
			"Mysql + pymysql: / / root: 123456 @127.0.0.1:3306 / test".Mysql +pymysql do not add Spaces between mysql and pymysql
			# "mysql + pymysql://root:root@localhost/test",
			max_overflow = 5.# The maximum number of external links that can be created after the connection pool size is exceeded
			pool_size = 10.# connection pool size
			echo = True.Display debugging information
		)
	my_database()
my_dabase_big()
metadata = MetaData() Get the element data and introduce the database
# -- -- -- -- -- -- -- -- -- -- -- -- when you create good after the table above, you can delete the above code a create table -- -- -- -- -- -- -- -- -- -- -- --
test=Table('test',metadata,
          Column('id',Integer(),primary_key=True,autoincrement=True),
          We can store all kinds of columns,
          The first is the name of our field (give it a name).
          # The second is type (data type)
          Increment increment increment increment increment increment increment increment increment increment increment increment increment increment increment increment increment increment increment increment
          Column('name',String(255)),
           Next comes our first field, name, which we define as a maximum of 255 bytes
          Column('data',DateTime(),default=datetime.now,onupdate=datetime.now,
          # second we store the date, then we use default which is our current time
          Column('man',Boolean(),default=False),
           # man is not male, using a Boolean (Bookean()) default=False
           )
# -- -- -- -- -- -- -- -- -- -- -- -- when you create good after the table above, also can not delete the code to create, this sentence can be deleted: metadata. Create_all (engine) # create data table -- -- -- -- -- -- -- -- -- -- -- --
conn = engine.connect() Get a connection
a = conn.begin()  # Start a thing
# Then, then. We're going to do it

try:
	Insert lol3 into the table below: name
	conn.execute(test.insert(),{'name':'lol3'})
	print(1/0) # here we deliberately error code to rollback()
	a.cmmit() The final submission is ok

except:
	a.rollback()
# Let's focus on this thing rollback:
# above try:... except:... Insert into the database table before we intentionally fail.
If the code fails, the rollback will undo the changes you just made (or the rollback, which is similar to not being performed). For example, the Control + Z) operation in the text operation.
# Because, I (program) has an error, to ensure the integrity of the database table, similar to file open must be closed.

Copy the code

Common data types

The name of the	keywords
field	Column, string, and INTEGER are all fields
MetaData	Is additional information about the table structure
The index	Index
table	Table
Operation method	Execute,update, INSERT, SELECT, delete, join, etc

Index of 4.

Database index creation can greatly improve system performance.

The first	Creating a unique index ensures the uniqueness of each row in a database table.
The second	Can greatly speed up the retrieval of data, which is the main reason to create indexes;
The third	Can speed up joins between tables, especially in terms of achieving referential integrity of data;
The fourth	When using the grouping and sorting clauses for data retrieval, the grouping and sorting time within a query can also be significantly reduced
The fifth	By using indexes, you can improve the performance of the system by using optimization hiders during the query process.

test = Table('host',metadata,
            Column('id',Integer(),primary_key=True),
            Column(('ip'),String(255),index=True),Copy the code

PS: For example, we create a host table, where one is id and one is IP. If we store a lot of ids and IP, among which there are 120 and 127, if I want to query the data starting with 127, but I need to search one by one, it will be much slower in terms of time speed.

However, if we add index=True to the code, the index will be created. What is this? When we query the beginning of 127, it only queries the index of 127. This will speed things up.

Note, however, that you can only use indx=True if the two data categories are distinct (like male/female). This is an optimization, and this is something you can write or not write. In the early stage, we do not need to write too many indexes, because if we write too many indexes, the system performance will deteriorate. The index index was added after careful consideration by the database engineer. But careful

Next, create table structure. Above we create table test, next we create table user:

""" # -*- coding: Utf-8 -*- # @author: AI Development_tool: PyCharm # code is far away from bugs with the god animal protecting I love animals. They taste delicious ┏ ┛ ┻ ━ ━ ━ ┛ ┻ ┓ ┃ ☃ ┃ ┃ ┳ ┛ ┗ ┳ ┃ ┃ ┻ ┃ ┗ ━ ┓ ┏ ━ ┛ ┃ ┗ ━ ━ ━ ┓ ┃ beast god bless ┣ ┓ ┃ never BUG! ┏ ┛ ┗ ┓ ┓ ┏ ━ ┳ ┓ ┏ ┛ ┃ ┫ ┫ ┃ ┫ ┫ ┗ ┻ ┛ ┗ ┻ ┛ "" "
Connect to the database
from sqlalchemy import create_engine,MetaData,Table,engine
from sqlalchemy import Column,String,Integer,DateTime,Boolean


engine = create_engine(
	"Mysql + pymysql: / / root: 123456 @127.0.0.1:3306 / test".Mysql +pymysql do not add Spaces between mysql and pymysql
	# "mysql + pymysql://root:root@localhost/test",
	max_overflow = 5.# The maximum number of external links that can be created after the connection pool size is exceeded
	pool_size = 10.# connection pool size
	echo = True.Display debugging information
)


metadata = MetaData() Get metadata, introduce the database
# define table
user = Table('user',metadata,
             Database table name, element data
             Column('id',Integer,primary_key=True,autoincrement=True),
             Column('name',String(10)))
metadata.create_all(engine) Create table

# -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- - to run the program output -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -
"C:\Program Files\Python37\python.exe"D:/daima/pycharm_daima/ database_2.py2019- 09 -29 20:32:31.984 INFO sqlalchemy.engine.base.Engine SHOW VARIABLES LIKE 'sql_mode'
2019- 09 -29 20:32:31.984 INFO sqlalchemy.engine.base.Engine {}
C:\Program Files\Python37\lib\site-packages\pymysql\cursors.py:170: Warning: (1366."Incorrect string value: '\\xBB\\xC6\\xBC\\xD2\\xB1\\xA6... ' for column 'VARIABLE_VALUE' at row 75") result = self._query(query)  C:\Program Files\Python37\lib\site-packages\pymysql\cursors.py:170: Warning: (1366."Incorrect string value: '\\xBB\\xC6\\xBC\\xD2\\xB1\\xA6... ' for column 'VARIABLE_VALUE' at row 404")
  result = self._query(query)
C:\Program Files\Python37\lib\site-packages\pymysql\cursors.py:170: Warning: (1366."Incorrect string value: '\\xBB\\xC6\\xBC\\xD2\\xB1\\xA6... ' for column 'VARIABLE_VALUE' at row 455")
  result = self._query(query)
C:\Program Files\Python37\lib\site-packages\pymysql\cursors.py:170: Warning: (1366."Incorrect string value: '\\xD6\\xD0\\xB9\\xFA\\xB1\\xEA... ' for column 'VARIABLE_VALUE' at row 484")
  result = self._query(query)
2019- 09 -29 20:32:31.990 INFO sqlalchemy.engine.base.Engine SHOW VARIABLES LIKE 'lower_case_table_names'
2019- 09 -29 20:32:31.990 INFO sqlalchemy.engine.base.Engine {}
2019- 09 -29 20:32:31.993 INFO sqlalchemy.engine.base.Engine SELECT DATABASE()
2019- 09 -29 20:32:31.993 INFO sqlalchemy.engine.base.Engine {}
2019- 09 -29 20:32:31.994 INFO sqlalchemy.engine.base.Engine show collation where `Charset` = 'utf8mb4' and `Collation` = 'utf8mb4_bin'
2019- 09 -29 20:32:31.994 INFO sqlalchemy.engine.base.Engine {}
2019- 09 -29 20:32:31.996 INFO sqlalchemy.engine.base.Engine SELECT CAST('test plain returns' AS CHAR(60)) AS anon_1
2019- 09 -29 20:32:31.996 INFO sqlalchemy.engine.base.Engine {}
2019- 09 -29 20:32:31.996 INFO sqlalchemy.engine.base.Engine SELECT CAST('test unicode returns' AS CHAR(60)) AS anon_1
2019- 09 -29 20:32:31.996 INFO sqlalchemy.engine.base.Engine {}
2019- 09 -29 20:32:31.997 INFO sqlalchemy.engine.base.Engine SELECT CAST('test collated returns' AS CHAR CHARACTER SET utf8mb4) COLLATE utf8mb4_bin AS anon_1
2019- 09 -29 20:32:31.997 INFO sqlalchemy.engine.base.Engine {}
2019- 09 -29 20:32:31.998 INFO sqlalchemy.engine.base.Engine DESCRIBE `user`
2019- 09 -29 20:32:31.998 INFO sqlalchemy.engine.base.Engine {}
2019- 09 -29 20:32:32.000 INFO sqlalchemy.engine.base.Engine ROLLBACK
2019- 09 -29 20:32:32,002 INFO sqlalchemy.engine.base.Engine 
CREATE TABLE user (
	id INTEGER NOT NULL AUTO_INCREMENT, 
	name VARCHAR(10), 
	PRIMARY KEY (id))2019- 09 -29 20:32:32,002 INFO sqlalchemy.engine.base.Engine {}
2019- 09 -29 20:32:32,055 INFO sqlalchemy.engine.base.Engine COMMIT

Process finished with exit code 0

Copy the code

PS: inside run results if there is a warning, it is good to ignore the coding problem, ancient legacy problems.

Note: there is a statement in the output result, let’s have a look.

CREATE TABLE user (
	id INTEGER NOT NULL AUTO_INCREMENT, 
	name VARCHAR(10), 
	PRIMARY KEY (id))Copy the code

Our code has been converted to the most native database code and can be used directly.

The table was created successfully:

mysql> show tables;
+----------------+
| Tables_in_test |
+----------------+
| test           |
| user           |
+----------------+
2 rows in set (0.00 sec)

mysql>

# Zero-based little white care
# If you are logged out or don't know why show tables; If the result is not correct, close the database and follow these steps:
（1Mysql -u root -p1234562)use test; (3)show tables;
# Operation process demonstration:MySql $MySql -u root -p123456 MySql: MySql $MySql -u root -p123456 MySql [Warning] Using a password on the command line interface can be insecure. Welcome to the MySQL monitor. Commandsend with ; or \g.
Your MySQL connection id is 11
Server version: 5.7.23 MySQL Community Server (GPL)

Copyright (c) 2000, 2018, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help; ' or '\h' for help. Type '\c' to clear the current input statement. mysql>use test;
Database changed
mysql> show tables;
+----------------+
| Tables_in_test |
+----------------+
| test           |
| user           |
+----------------+
2 rows in set (0.00 sec)

mysql>
The output is the same as above
mysql> show tables;
+----------------+
| Tables_in_test |
+----------------+
| test           |
| user           |
+----------------+
2 rows in set (0.00 sec)

mysql>
Copy the code

We can also look at the structure of this table:

Mysql > alter table user;
mysql> desc user;
+-------+-------------+------+-----+---------+----------------+
| Field | Type        | Null | Key | Default | Extra          |
+-------+-------------+------+-----+---------+----------------+
| id    | int(11)     | NO   | PRI | NULL    | auto_increment |
| name  | varchar(10) | YES  |     | NULL    |                |
+-------+-------------+------+-----+---------+----------------+
2 rows in set (0.01 sec)

mysql>
Copy the code

PRI: primary key >>> primary_key=True

Let’s start with a quick review of the database command line key statements:

# display database
show databases;

# Use database
use test;
# (test: name of your database)

Check table structure
desc user;
# (user: name of your table)

# query statement
select * from user_table;
# (user_table: your database table name)

SQL > select * from table_name
show tables;

Copy the code

3.4 Adding Data (Native Statement) Method 1

Connect to the database
from sqlalchemy import create_engine,MetaData,Table,engine
from sqlalchemy import Column,String,Integer,DateTime,Boolean


engine = create_engine(
	"Mysql + pymysql: / / root: 123456 @127.0.0.1:3306 / test".Mysql +pymysql do not add Spaces between mysql and pymysql
	# "mysql + pymysql://root:root@localhost/test",
	max_overflow = 5.# The maximum number of external links that can be created after the connection pool size is exceeded
	pool_size = 10.# connection pool size
	echo = True.Display debugging information
)
engine.execute("insert into user (name) values ('lyy')")
engine.execute("Insert into user (name) values ('AI ')")


Check the insert result
mysql> select * from user;
+----+--------+
| id | name   |
+----+--------+
|  1 | lyy    |
|  2AI yue "| | + - + -- -- -- -- -- -- -- -- +4 rows in set (0.00 sec)

mysql>
Copy the code

INSERT INTO TABLE (KEY1,KEYA) VALUES (VALUE1,VALUE2); UPDATE TABLE SET KEY=VALUE, KEY=VALUE WHERE...; SELECT * FROM TABLE; DELETE FROM TABLE WHERE ···; Delete statementCopy the code

Complete operation example:

""" # -*- coding: Utf-8 -*- # @author: AI Development_tool: PyCharm # code is far away from bugs with the god animal protecting I love animals. They taste delicious ┏ ┛ ┻ ━ ━ ━ ┛ ┻ ┓ ┃ ☃ ┃ ┃ ┳ ┛ ┗ ┳ ┃ ┃ ┻ ┃ ┗ ━ ┓ ┏ ━ ┛ ┃ ┗ ━ ━ ━ ┓ ┃ beast god bless ┣ ┓ ┃ never BUG! ┏ ┛ ┗ ┓ ┓ ┏ ━ ┳ ┓ ┏ ┛ ┃ ┫ ┫ ┃ ┫ ┫ ┗ ┻ ┛ ┗ ┻ ┛ "" "
Connect to the database
from sqlalchemy import create_engine,MetaData,Table,engine
from sqlalchemy import Column,String,Integer


engine = create_engine(
	"Mysql + pymysql: / / root: 123456 @127.0.0.1:3306 / test".Mysql +pymysql do not add Spaces between mysql and pymysql
	# "mysql + pymysql://root:root@localhost/test",
	max_overflow = 5.# The maximum number of external links that can be created after the connection pool size is exceeded
	pool_size = 10.# connection pool size
	echo = True.Display debugging information
)

# --------- create table -------------
metadata = MetaData() Get metadata, introduce the database
# define table
user = Table('user',metadata,
             Database table name, element data
             Column('id',Integer,primary_key=True,autoincrement=True),
             Column('name',String(10)))
metadata.create_all(engine) Create table

# --------------- Add, delete, check and change the operation --------------

# add data
engine.execute("Insert into user (name) values ('AI ')")
# update data
engine.execute("update user set id=5,name='python' where id =5;")
# Update data method 2
engine.execute("update user set name='python' where id =5;")
# Update data method 3
engine.execute("update user set name='20191001'")
# delete data
# engine.execute("delete from user") #
# delete the specified position
# engine.execute("delete from user where id=2")
# view data
a = engine.execute("select * from user")
# print(a)
for text in a:
	print(text)
	print(type(text))

Copy the code

3.5 Use the table structure to add, delete, modify and search — Method 2

Using the encapsulated method to avoid writing complex low-level mysql statements, let’s delete the user table above for clarity. Create a new user_table.

Let’s see what tables we have in our database:

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| bool| | choose | | lesson | | look | | look_1 | | mysql | | p | | performance_schema | | pyspider | | python_2019 | | runoob  | | sys | | test | | web_info | +--------------------+15 rows in set (0.00 sec)

mysql> use test;
Database changed
mysql> show tables;
Empty set (0.00 sec)

mysql>
Copy the code

As you can see, there is no table under our test database, so let’s create the table. Create a user_table:

from sqlalchemy import create_engine,MetaData,Table,engine
from sqlalchemy import Column,String,Integer


engine = create_engine(
	"Mysql + pymysql: / / root: 123456 @127.0.0.1:3306 / test".Mysql +pymysql do not add Spaces between mysql and pymysql
	# "mysql + pymysql://root:root@localhost/test",
	max_overflow = 5.# The maximum number of external links that can be created after the connection pool size is exceeded
	pool_size = 10.# connection pool size
	echo = True.Display debugging information
)

metadata = MetaData() Get metadata, introduce the database

# define table
user_table = Table('user_table', metadata,
                   Column("id", Integer, primary_key=True,autoincrement=True),
                   Column("Teaching table",String(10)))
metadata.create_all(engine) # to create table

# Result of running code
"C:\Program Files\Python37\python.exe"D:/daima/ PyCharm_daima /database_num_4.py2019- 09 -30 20:42:46.589 INFO sqlalchemy.engine.base.Engine SHOW VARIABLES LIKE 'sql_mode'
2019- 09 -30 20:42:46.589 INFO sqlalchemy.engine.base.Engine {}
C:\Program Files\Python37\lib\site-packages\pymysql\cursors.py:170: Warning: (1366."Incorrect string value: '\\xBB\\xC6\\xBC\\xD2\\xB1\\xA6... ' for column 'VARIABLE_VALUE' at row 75")
  result = self._query(query)
C:\Program Files\Python37\lib\site-packages\pymysql\cursors.py:170: Warning: (1366."Incorrect string value: '\\xBB\\xC6\\xBC\\xD2\\xB1\\xA6' for column 'VARIABLE_VALUE' at row 94")
  result = self._query(query)
C:\Program Files\Python37\lib\site-packages\pymysql\cursors.py:170: Warning: (1366."Incorrect string value: '\\xBB\\xC6\\xBC\\xD2\\xB1\\xA6... ' for column 'VARIABLE_VALUE' at row 259")
  result = self._query(query)
C:\Program Files\Python37\lib\site-packages\pymysql\cursors.py:170: Warning: (1366."Incorrect string value: '\\xBB\\xC6\\xBC\\xD2\\xB1\\xA6... ' for column 'VARIABLE_VALUE' at row 377")
  result = self._query(query)
C:\Program Files\Python37\lib\site-packages\pymysql\cursors.py:170: Warning: (1366."Incorrect string value: '\\xBB\\xC6\\xBC\\xD2\\xB1\\xA6... ' for column 'VARIABLE_VALUE' at row 403")
  result = self._query(query)
C:\Program Files\Python37\lib\site-packages\pymysql\cursors.py:170: Warning: (1366."Incorrect string value: '\\xBB\\xC6\\xBC\\xD2\\xB1\\xA6... ' for column 'VARIABLE_VALUE' at row 404")
  result = self._query(query)
C:\Program Files\Python37\lib\site-packages\pymysql\cursors.py:170: Warning: (1366."Incorrect string value: '\\xBB\\xC6\\xBC\\xD2\\xB1\\xA6... ' for column 'VARIABLE_VALUE' at row 455")
  result = self._query(query)
C:\Program Files\Python37\lib\site-packages\pymysql\cursors.py:170: Warning: (1366."Incorrect string value: '\\xD6\\xD0\\xB9\\xFA\\xB1\\xEA... ' for column 'VARIABLE_VALUE' at row 484")
  result = self._query(query)
2019- 09 -30 20:42:46.594 INFO sqlalchemy.engine.base.Engine SHOW VARIABLES LIKE 'lower_case_table_names'
2019- 09 -30 20:42:46.594 INFO sqlalchemy.engine.base.Engine {}
2019- 09 -30 20:42:46.597 INFO sqlalchemy.engine.base.Engine SELECT DATABASE()
2019- 09 -30 20:42:46.597 INFO sqlalchemy.engine.base.Engine {}
2019- 09 -30 20:42:46.597 INFO sqlalchemy.engine.base.Engine show collation where `Charset` = 'utf8mb4' and `Collation` = 'utf8mb4_bin'
2019- 09 -30 20:42:46.597 INFO sqlalchemy.engine.base.Engine {}
2019- 09 -30 20:42:46.599 INFO sqlalchemy.engine.base.Engine SELECT CAST('test plain returns' AS CHAR(60)) AS anon_1
2019- 09 -30 20:42:46.599 INFO sqlalchemy.engine.base.Engine {}
2019- 09 -30 20:42:46.599 INFO sqlalchemy.engine.base.Engine SELECT CAST('test unicode returns' AS CHAR(60)) AS anon_1
2019- 09 -30 20:42:46.599 INFO sqlalchemy.engine.base.Engine {}
2019- 09 -30 20:42:46.600 INFO sqlalchemy.engine.base.Engine SELECT CAST('test collated returns' AS CHAR CHARACTER SET utf8mb4) COLLATE utf8mb4_bin AS anon_1
2019- 09 -30 20:42:46.600 INFO sqlalchemy.engine.base.Engine {}
2019- 09 -30 20:42:46.601 INFO sqlalchemy.engine.base.Engine DESCRIBE `user_table`
2019- 09 -30 20:42:46.601 INFO sqlalchemy.engine.base.Engine {}
2019- 09 -30 20:42:46.602 INFO sqlalchemy.engine.base.Engine ROLLBACK
2019- 09 -30 20:42:46.605 INFO sqlalchemy.engine.base.Engine 
CREATE TABLE user_table (
	idINTEGER NOT NULL AUTO_INCREMENT, VARCHAR(10), 
	PRIMARY KEY (id))2019- 09 -30 20:42:46.605 INFO sqlalchemy.engine.base.Engine {}
2019- 09 -30 20:42:46.650 INFO sqlalchemy.engine.base.Engine COMMIT

Process finished with exit code 0

# Database result after run
mysql> use test;
Database changed
mysql> show tables;
+----------------+
| Tables_in_test |
+----------------+
| user_table     |
+----------------+
1 row in set (0.00 sec)

mysql>
Copy the code

We have successfully created the user_Table data table, so let me add one more data, HJB

Alter table data
conn = engine.connect() Get a connection
# # Add data
conn.execute(user_table.insert(),{"Teaching table":"hjb"})
# Close to prevent memory overflow
conn.close()# close the connection

Let's look at the data in the table
mysql> select * from user_table;
+----+--------+
| idTeaching table | | + - + -- -- -- -- -- -- -- -- + |1 | hjb    |
+----+--------+
1 row in set (0.00 sec)

mysql>
Copy the code

We successfully added data: HJB. To make the rest of the tutorial more intuitive, let’s add another data hjb_two.

mysql> select * from user_table;
+----+---------+
| idTeaching table | | + - + -- -- -- -- -- -- -- -- -- + |1 | hjb     |
|  2 | hjb_two |
+----+---------+
2 rows in set (0.00 sec)

mysql>
Copy the code

# Update data - Update all data
conn.execute(user_table.update(),{"Teaching table":"AI yue chong"})
conn.close()

Run the program database result
mysql> select * from user_table;
+----+--------+
| idTeaching table | | + - + -- -- -- -- -- -- -- -- + |1| AI yue "| |2AI yue "| | + - + -- -- -- -- -- -- -- -- +2 rows in set (0.00 sec)

mysql>

All updates are successful
How do you specify a data modification?
conn.execute(user_table.update().where(user_table.c.id= =1).values(id=1000))
conn.close()

# change id 1 to 1000
# database result
mysql> select * from user_table;
+------+--------+
| idTeaching table | | + -- -- -- -- -- - + -- -- -- -- -- -- -- -- + |2| AI yue "| |1000AI yue "| | + -- -- -- -- -- - + -- -- -- -- -- -- -- -- +2 rows in set (0.00 sec)

mysql>

# modify data
conn.execute(user_table.update().where(user_table.c.id= =2Values (teaching table ='AIYC'))
conn.close()

Database result after run
mysql> select * from user_table;
+------+--------+
| idTeaching table | | + -- -- -- -- -- - + -- -- -- -- -- -- -- -- + |2 | AIYC   |
| 1000AI yue "| | + -- -- -- -- -- - + -- -- -- -- -- -- -- -- +2 rows in set (0.00 sec)

mysql>
Copy the code

The complete code for method 2:

from sqlalchemy import create_engine,MetaData,Table,engine
from sqlalchemy import Column,String,Integer


engine = create_engine(
	"Mysql + pymysql: / / root: 123456 @127.0.0.1:3306 / test".Mysql +pymysql do not add Spaces between mysql and pymysql
	# "mysql + pymysql://root:root@localhost/test",
	max_overflow = 5.# The maximum number of external links that can be created after the connection pool size is exceeded
	pool_size = 10.# connection pool size
	echo = True.Display debugging information
)

metadata = MetaData() Get metadata, introduce the database

# define table
user_table = Table('user_table', metadata,
                   Column("id", Integer, primary_key=True,autoincrement=True),
                   Column("Teaching table",String(10)))
metadata.create_all(engine) # to create table

Alter table data
conn = engine.connect() Get a connection
# add data
conn.execute(user_table.insert(),{"Teaching table":"hjb_two"})
# # Update data - Update all data
conn.execute(user_table.update(),{"Teaching table":"AI yue chong"})
# # Update specified data
conn.execute(user_table.update().where(user_table.c.id= =1).values(id=1000))
conn.execute(user_table.update().where(user_table.c.id= =2Values (teaching table ='AIYC'))
# # where(user.cid ==2) where(user.cid ==2
conn.close()
Copy the code

Next is the query statement, which requires us to import select:

from sqlalchemy import create_engine,MetaData,Table,engine
from sqlalchemy import Column,String,Integer,select


engine = create_engine(
	"Mysql + pymysql: / / root: 123456 @127.0.0.1:3306 / test".Mysql +pymysql do not add Spaces between mysql and pymysql
	# "mysql + pymysql://root:root@localhost/test",
	max_overflow = 5.# The maximum number of external links that can be created after the connection pool size is exceeded
	pool_size = 10.# connection pool size
	echo = True.Display debugging information
)

metadata = MetaData() Get metadata, introduce the database

# define table
user_table = Table('user_table', metadata,
                   Column("id", Integer, primary_key=True,autoincrement=True),
                   Column("Teaching table",String(10)))
metadata.create_all(engine) # to create table

Alter table data
conn = engine.connect() Get a connection
# add data
conn.execute(user_table.insert(),{"Teaching table":"hjb_two"})
# # Update data - Update all data
conn.execute(user_table.update(),{"Teaching table":"AI yue chong"})
# # Update specified data
conn.execute(user_table.update().where(user_table.c.id= =1).values(id=1000))
data = input('Please you input data:>>>')
conn.execute(user_table.update().where(user_table.c.id= =1000).values(table =data)# # where(user.cid ==2) where(user.cid ==2
# query statement
Find all contents of the teaching tableRes = conn.execute(select([user_table.c.print(res.fetchall())
# output
# [(AIYC,), (' AI yue chong,), (' AI yue chong,), (' AI yue chong,), (' AI yue chong,), (' AI yue chong,), (' AI yue chong,), (' AI yue chong,), (' AI yue chong,), (' AI yue chong,), (' AI yue chong,)]
for i in res:
	print(i)
# output
"" "(AIYC,) (' AI yue chong,) (' AI yue chong,) (' AI yue chong,) (' AI yue chong,) (' AI yue chong,) (' AI yue chong,) (' AI yue chong,) (' AI yue chong,) (' AI yue chong,) (' AI yue chong,) ('AI Yue Chuang ',) ('AI Yue Chuang ',) """
# delete data
conn.execute(user_table.delete().where(user_table.c.id= =2))
# id==2 delete the specified data
conn.close()
Copy the code

3.6 Integrating ORM Class Manipulation Data — Method 3 (Recommended)

from sqlalchemy import create_engine,Column,String,Integer
from sqlalchemy.orm import sessionmaker
from sqlalchemy.ext.declarative import  declarative_base # import our base class, that is, we create classes based on it
# sessionmaker instead of conn = engine.connect()
Conn = engine.connect(
Connect to database
engine = create_engine(
	"Mysql + pymysql: / / root: 123456 @127.0.0.1:3306 / test".Mysql +pymysql do not add Spaces between mysql and pymysql
	# "mysql + pymysql://root:root@localhost/test",
	max_overflow = 5.# The maximum number of external links that can be created after the connection pool size is exceeded
	pool_size = 10.# connection pool size
	echo = True.Display debugging information
)
Base = declarative_base()
class Host(Base) :
	The table name is hosts
	__tablename__='hosts'
	# table structure
	# primary_key = primary key
	# when the only unique
	# unllable is not empty
	id = Column(Integer,primary_key=True,autoincrement=True)
	# unique is similar to a twitter backend or other operation that cannot be repeated, and also implements incremental crawlers
	# nullable is similar to # nullable: a user has filled in a nullable character on the registration page, which is not allowed to be null
	hostname = Column(String(64),unique=True,nullable=False)
	ip_addr = Column(String(128),unique=True,nullable=False)
	port = Column(Integer, default=22)
	title = Column(String(200))


Base.metadata.create_all(engine) # to create table
Copy the code

Use sessionMaker to bind engine, then instantiate session to bind engine, then use session to CURD >>> Create, Update, Read, Delete.

Add, delete, change and check:

# -*- coding: utf-8 -*-
Write code is love, write the world full of love!

from sqlalchemy import create_engine,Column,String,Integer
from sqlalchemy.orm import sessionmaker
from sqlalchemy.ext.declarative import  declarative_base # import our base class, that is, we create classes based on it
# sessionmaker instead of conn = engine.connect()
Conn = engine.connect(
Connect to database
engine = create_engine(
	"Mysql + pymysql: / / root: 123456 @127.0.0.1:3306 / test".Mysql +pymysql do not add Spaces between mysql and pymysql
	# "mysql + pymysql://root:root@localhost/test",
	max_overflow = 5.# The maximum number of external links that can be created after the connection pool size is exceeded
	pool_size = 10.# connection pool size
	echo = True.Display debugging information
)
Base = declarative_base()
class Host(Base) :
	The table name is hosts
	__tablename__='hosts'
	# table structure
	# primary_key = primary key
	# when the only unique
	# unllable is not empty
	id = Column(Integer,primary_key=True,autoincrement=True)
	# unique is similar to a twitter backend or other operation that cannot be repeated, and also implements incremental crawlers
	# nullable is similar to # nullable: a user has filled in a nullable character on the registration page, which is not allowed to be null
	hostname = Column(String(64),unique=True,nullable=False)
	ip_addr = Column(String(128),unique=True,nullable=False)
	port = Column(Integer, default=22)
Create a table and comment it out
# base.metadata. create_all(engine

if __name__ == '__main__':
	try:
		Session = sessionmaker(bind=engine)
		sess = Session() # create instance
		h = Host(hostname='test1',ip_addr="127.0.0.1")
		h2 = Host(hostname='test2',ip_addr="192.168.0.1",port=8000)
		h3= Host(hostname='test3',ip_addr="192.168.1.1",port=8080)
		sess.add(h) # Add one at a time
		sess.add_all([h2,h3]) # add multiple at a time
		sess.commit() # Things submitted, must have
	except:
		print("Error!")
Copy the code

if __name__ == '__main__':
	try:
		Session = sessionmaker(bind=engine)
		sess = Session() # create instance
		h = Host(hostname='test1',ip_addr="127.0.0.1")
		h2 = Host(hostname='test2',ip_addr="192.168.0.1",port=8000)
		h3= Host(hostname='test3',ip_addr="192.168.1.1",port=8080)
		sess.add(h) # Add one at a time
		sess.add_all([h2,h3]) # add multiple at a time
		# update
		sess.query(Host).filter(Host.id= =1).update({"port":2019})
		# Host create table name, filter (host. id==1)
		# remove
		sess.query(Host).filter(Host.id= =1).delete()
		# query
		res = sess.query(Host).filter_by(id=1).all(a)# res = sess.query(Host).filter(Host.id==1).all()
		# filter_by(id==1)
		for r in res:
			print(r.hostname)
			print(r.ip_addr)
			print(r.port)
		sess.commit() # Things submitted, must have
	except:
		print("Error!")
Copy the code

Four, relational database advanced

4.1 a one-to-many

Design database tables, programmer websites

• Single table: A large table
• Multiple tables: programmer tables and language tables used by programmers

ID-int	Name-str	Gender-str	From-str
Advantage-str	Disadvantage-str	DateTime-datetime.datetime	CodeName-str

In fact, if in actual development, when each programmer enters the site (register the site), if each programmer creates a separate database table as soon as they register, such database redundancy is too much.

Why do you say that?

Because, if a lot of programmers are writing code in Python, which is CodeName in the table above, then this will be repeated over and over again. So why don’t we keep a separate table of repeated and unchanging data? Just call it when you use it. Changes such as programmer name, age, seniority, etc. to create a table.

Moreover, most programmers know a lot of programming languages, that is, many languages for every programmer.

For example:

4.4.1 built table

PS: Students can add charset=UTF8MB4 if there is a coding problem in the code from last class.

# -*- coding: utf-8 -*-
Write code is love, write the world full of love!
# @author: DateTime: 2019/10/1 16:58 @function: Function Development_tool: PyCharm

from sqlalchemy import create_engine,Column,Integer,String,ForeignKey
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import sessionmaker,relationship
# To build a one-to-many approach, you must use relationship

Connect to database
engine = create_engine(
	"Mysql + pymysql: / / root: 123456 @127.0.0.1:3306 / test? charset=UTF8MB4".Mysql +pymysql do not add Spaces between mysql and pymysql
	# "mysql + pymysql://root:root@localhost/test",
	max_overflow = 5.# The maximum number of external links that can be created after the connection pool size is exceeded
	pool_size = 10.# connection pool size
	echo = True.Display debugging information
)

Base = declarative_base()
# <-----------------user_table----------------->
class User(Base) :
	__tablename__="user"
	# table structure
	# primary_key = primary key
	# when the only unique
	# nullable is not empty
	# nullable = False: nullable is not allowed
	# nullable = True: nullable allowed
	id = Column(Integer(), primary_key=True, autoincrement=True)
	name = Column(String(125), nullable=True)
	advantage = Column(String(125), nullable=True)
	disadvantage = Column(String(125), nullable=True)
	town = Column(String(125),nullable=True)
	language = relationship("Language", backref="user")

class Language(Base) :
	__tablename__="language"
	id = Column(Integer(), primary_key=True, autoincrement=True)
	name = Column(String(125),nullable=True)
	advantage = Column(String(125), nullable=True)
	disadvantage = Column(String(125), nullable=True)
	user_id = Column(Integer(), ForeignKey("user.id"))
Copy the code

We need to create the table before adding data
Base.metadata.create_all(engine)
Copy the code

After creating the table, run the following command:

mysql> use test;
Database changed
mysql> desc user;
+--------------+--------------+------+-----+---------+----------------+
| Field        | Type         | Null | Key | Default | Extra          |
+--------------+--------------+------+-----+---------+----------------+
| id           | int(11)      | NO   | PRI | NULL    | auto_increment |
| name         | varchar(125) | YES  |     | NULL    |                |
| gender       | varchar(125) | YES  |     | NULL    |                |
| advantage    | varchar(125) | YES  |     | NULL    |                |
| disadvantage | varchar(125) | YES  |     | NULL    |                |
| town         | varchar(125) | YES  |     | NULL    |                |
+--------------+--------------+------+-----+---------+----------------+
6 rows in set (0.00 sec)

mysql> desc language;
+--------------+--------------+------+-----+---------+----------------+
| Field        | Type         | Null | Key | Default | Extra          |
+--------------+--------------+------+-----+---------+----------------+
| id           | int(11)      | NO   | PRI | NULL    | auto_increment |
| name         | varchar(125) | YES  |     | NULL    |                |
| advantage    | varchar(125) | YES  |     | NULL    |                |
| disadvantage | varchar(125) | YES  |     | NULL    |                |
| user_id      | int(11)      | YES  | MUL | NULL    |                |
+--------------+--------------+------+-----+---------+----------------+
5 rows in set (0.00 sec)

mysql> select * from user;
+----+------+--------+-----------+--------------+------+
| id | name | gender | advantage | disadvantage | town |
+----+------+--------+-----------+--------------+------+
|  1| threes male | | NULL | NULL | Beijing | |2Female | and | | NULL | NULL | | tianjin + - + - + -- -- -- -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- - +2 rows in set (0.00 sec)

mysql> select * from language;
+----+--------+-----------+--------------+---------+
| id | name   | advantage | disadvantage | user_id |
+----+--------+-----------+--------------+---------+
|  1| Python slow | | development | fast run1 |
+----+--------+-----------+--------------+---------+
1 row in set (0.00 sec)

mysql>
Copy the code

# -*- coding: utf-8 -*-
Write code is love, write the world full of love!
# @author: DateTime: 2019/10/1 16:58 @function: Function Development_tool: PyCharm

from sqlalchemy import create_engine,Column,Integer,String,ForeignKey
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import sessionmaker,relationship
# To build a one-to-many approach, you must use relationship

Connect to database
engine = create_engine(
	"Mysql + pymysql: / / root: 123456 @127.0.0.1:3306 / test".Mysql +pymysql do not add Spaces between mysql and pymysql
	# "mysql + pymysql://root:root@localhost/test",
	max_overflow = 5.# The maximum number of external links that can be created after the connection pool size is exceeded
	pool_size = 10.# connection pool size
	echo = True.Display debugging information
)

Base = declarative_base()
# <-----------------user_table----------------->
class User(Base) :
	__tablename__="user"
	# table structure
	# primary_key = primary key
	# when the only unique
	# nullable is not empty
	# nullable = False: nullable is not allowed
	# nullable = True: nullable allowed
	id = Column(Integer(), primary_key=True, autoincrement=True)
	name = Column(String(125), nullable=True)
	gender = Column(String(125),nullable=True)
	advantage = Column(String(125), nullable=True)
	disadvantage = Column(String(125), nullable=True)
	town = Column(String(125),nullable=True)
	# relationship associated
	# This line of code: optional
	language = relationship("Language", backref="user")
This allows the language table to map directly and access data in the User table

# <-----------------language_table----------------->
class Language(Base) :
	__tablename__="language"
	id = Column(Integer(), primary_key=True, autoincrement=True)
	name = Column(String(125),nullable=True)
	advantage = Column(String(125), nullable=True)
	disadvantage = Column(String(125), nullable=True)
	Add user_id and write its foreign key is the id table in user
	user_id = Column(Integer(), ForeignKey("user.id"))


We need to create the table before adding data
# Base.metadata.create_all(engine)

# <-----------------add_data----------------->
if __name__ == '__main__':
# <--------------- method 1 --------------->
	Add data method 1
	Session = sessionmaker(engine)
	session = Session()
# add user
	user1 = User(name='Joe', gender="Male", town="Beijing")
	user2 = User(name='bill', gender="Female", town="Tianjin")
	session.add_all([user1, user2])
	session.commit()
# Add language
	language1 = Language(name="Python", advantage="Fast development", disadvantage="Running slow")
	# language2 = Language(name="C++", advantage=" developer ", advantage=" version ")
	language1.user = user1
	session.add(language1)
	session.commit()
# <--------------- method 2 --------------->
# add data at the same time
	Session = sessionmaker(engine)
	session = Session()
	user1 = User(name="AI yue chong", gender="Male",advantage="Python", disadvantage="Run_low" )
	user1.language = [
		Language(name="Python", advantage="Fast development", disadvantage="Running slow"),
		Language(name="C", advantage="Slow development", disadvantage="Run fast")
	]
	session.add(user1)
	session.commit()
Copy the code

---

4.1.2 Searching for Data

if __name__ == '__main__':
	Session = sessionmaker(engine)
	session = Session()
	user_select = session.query(User).filter_by(id=3).first()
	print("name:>>>", user_select.name)
	lan = session.query(Language).filter_by(user_id=user_select.id)
	for i in lan:
		print("language:>>>",i.name)
Copy the code

Querying the data yields a list. All data is all(), and the first data is first().

4.1.3 Deleting Data

When data is deleted, if there is associated data, the fields under the associated data will become NULL, which wastes space.

if __name__ == '__main__':
	Session = sessionmaker(engine)
	session = Session()
	use = session.query(User).filter(User.id= =3).first()
	session.delete(use)
	session.commit()
Copy the code

Before deleting:

mysql> select * from user;
+----+--------+--------+-----------+--------------+------+
| id | name   | gender | advantage | disadvantage | town |
+----+--------+--------+-----------+--------------+------+
|  1| threes male | | NULL | NULL | Beijing | |2Female | and | | NULL | NULL | | tianjin |3| AI yue "| | | male Python Run_low | NULL | +, + + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- - +3 rows in set (0.00 sec)

mysql> select * from language;
+----+--------+-----------+--------------+---------+
| id | name   | advantage | disadvantage | user_id |
+----+--------+-----------+--------------+---------+
|  1| Python slow | | development | fast run1 |
|  2| Python slow | | development | fast run3 |
|  3| | development | run slow fast | C3 |
+----+--------+-----------+--------------+---------+
3 rows in set (0.00 sec)

mysql>
Copy the code

After deletion:

mysql> select * from user;
+----+------+--------+-----------+--------------+------+
| id | name | gender | advantage | disadvantage | town |
+----+------+--------+-----------+--------------+------+
|  1| threes male | | NULL | NULL | Beijing | |2Female | and | | NULL | NULL | | tianjin + - + - + -- -- -- -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- - +2 rows in set (0.00 sec)

mysql> select * from language;
+----+--------+-----------+--------------+---------+
| id | name   | advantage | disadvantage | user_id |
+----+--------+-----------+--------------+---------+
|  1| Python slow | | development | fast run1 |
|  2| Python | development | fast run slow | NULL | |3| | development | run slow fast | NULL | C + + -- -- -- -- -- -- -- -- - + -- -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- -- -- -- -- +3 rows in set (0.00 sec)

mysql>
Copy the code

Let me take a screenshot of it to make it clearer:

As you can see, deleting data in this way will lead to data redundancy, so how do we do this?

At this point, you’ll see. Let’s just add this:

Base.metadata.drop_all(engine)
Copy the code

However, if you don’t want to delete, here’s how to recreate a database from the command line:

Let's create a database for test2
Let's start by looking at what databases we already have
But before we do that, we need to run mysql
mysql -u root -p
passowrd:****
You need to enter your database user name after -u, and press Enter to enter your database password.
# You can also log in with this:
mysql -u root -p123456
# 123456 Just write your password
SQL > select * from database;
show databases;
Create a database. Do not use the same name as an existing databaseCREATE DATABASE Specifies the DATABASE name.# Of course, lowercase is also okCreate database Specifies the database name.# create success and return:
Query OK, 1 row affected (0.00 sec)

Alter table drop table drop table
drop table;
# delete multiple files at once
drop table1 table;
If there is an association, delete the association table first.
# Ps: If you want to delete it in code, write it
drop_all()
# Delete is not recommended in the code, it is too dangerous
Copy the code

Operations so far (including creating and dropping tables) :

from sqlalchemy import create_engine,Column,Integer,String,ForeignKey
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import sessionmaker,relationship
# To build a one-to-many approach, you must use relationship

Connect to database
engine = create_engine(
	"Mysql + pymysql: / / root: 123456 @127.0.0.1:3306 / test".Mysql +pymysql do not add Spaces between mysql and pymysql
	# "mysql + pymysql://root:root@localhost/test",
	max_overflow = 5.# The maximum number of external links that can be created after the connection pool size is exceeded
	pool_size = 10.# connection pool size
	echo = True.Display debugging information
)

Base = declarative_base()
# <-----------------user_table----------------->
class User(Base) :
	__tablename__="user"
	# table structure
	# primary_key = primary key
	# when the only unique
	# nullable is not empty
	# nullable = False: nullable is not allowed
	# nullable = True: nullable allowed
	id = Column(Integer(), primary_key=True, autoincrement=True)
	name = Column(String(125), nullable=True)
	gender = Column(String(125),nullable=True)
	advantage = Column(String(125), nullable=True)
	disadvantage = Column(String(125), nullable=True)
	town = Column(String(125),nullable=True)
	# relationship associated
	# This line of code: optional
	language = relationship("Language", backref="user", cascade='all,delete')
This allows the language table to map directly and access data in the User table

# <-----------------language_table----------------->
class Language(Base) :
	__tablename__="language"
	id = Column(Integer(), primary_key=True, autoincrement=True)
	name = Column(String(125),nullable=True)
	advantage = Column(String(125), nullable=True)
	disadvantage = Column(String(125), nullable=True)
	Add user_id and write its foreign key is the id table in user
	# is associative operation
	user_id = Column(Integer(), ForeignKey("user.id"))

If you want to drop a table, do the following:
Base.metadata.drop_all(engine)
We need to create the table before adding data
Base.metadata.create_all(engine)

Copy the code

Add data, as above. This time, we delete the data again. And that’s it!

Suddenly thought of this sentence: delete library run.

4.1.4 Updating Data

if __name__ == '__main__':
	Session = sessionmaker(engine)
	session = Session()
	u = session.query(User).filter(User.id= =1).first()
	u.name = "Daisy"
	session.commit()
Copy the code

if __name__ == '__main__':
	Session = sessionmaker(engine)
	session = Session()
	u = session.query(User).filter(User.id= =4).first()
	lan = u.language[0].name='python3'
	session.commit()
Copy the code

4.1.5 Rollback of things

Transaction rollback, why do we need this operation?

For example: in our tmall order grab (although I did not grab), but if you fail to grab the order, in fact, in the code is similar to, your code suddenly runs wrong, then at this time you need to restore the previous operation, such as: select goods, product attributes, number of goods, promotion price and so on. These operations, in fact, are operating the data in the database, and at this time, you do not grab a successful order, you need to roll back your original operation.

Otherwise, you actually did not purchase, but the record in the database shows that you did purchase, so the data in the database is incomplete. Therefore, to ensure data integrity in the database, a rollback operation is required. Operations before recovery, that is, operations on the database before recovery.

So use try… except… .

In other words, before session.mit () (i.e. before commit data), the modified data is like in the memory buffer, perform the rollback operation to clear the buffer modified data. It’s always going to be this mechanism.

The last

This Chat is basically over, thank you very much for watching, due to too much content and text, in order to help you better clarify your thoughts, improve the reading effect, the following is the Chat summary.

1. Python syntax is a quick start. The language is the foundation
2. You have used the Python syntax to operate the MySQL database
3. Master the syntax and use of SQLAlchemy
4. Advanced involves many – to – many table joins and so on, simple involves part of

---

This article was first published in GitChat, shall not be reproduced without authorization, reprint to contact GitChat.