Shulou(Shulou.com)05/31 Report--

This article introduces how to use SQLAlchemy in Dataset, the content is very detailed, interested friends can refer to, hope to be helpful to you.

Dataset is very useful for manipulating JSON, CSV files, and NoSQL.

Import dataset

Connect to the MySQL database:

Db = dataset.connect ('mysql://username:password@10.10.10.10/ctf?charset=utf8')

User name: username, password: password, database address (address + port): 10.10.10.10 database name: ctf

Connect to the SQLite database:

Db = dataset.connect ('sqlite:///ctf.db')

Connect to the PostgreSQL database:

Db = dataset.connect ('postgresql://scott:tiger@localhost:5432/mydatabase')

Be sure to pay attention to specifying character encoding

Table = db ['city'] # (select city table) user = table (' name') # find all the data in the table 'name' column attribute res = db.query (' select name from table limit 10') # if you don't need to view all the data * use limit Because the loading of all the data is very time-consuming for x in res: print x ['name'] # Select the data of the name field table.insert (dict (name='John Doe', age=37)) table.insert (dict (name='Jane Doe', age=34, gender='female')) john = table.find_one (name='John Doe')

Search the database for data that satisfies multiple conditions at the same time: table.find_one (attribute 1 = attribute value 1, attribute 2 = attribute value 2,...)

Note: find_one is very slow.

Insert data

Dataset automatically creates table and field names based on input

Table = db ['user'] # or table = db.get_table (' user') table.insert (dict (name='John Doe', age=46, country='China')) table.insert (dict (name='Jane Doe', age=37, country='France', gender='female')) # automatic generation of primary key id

Update data

Table.update (dict (name='John Doe', age=47), ['name']) # the second parameter is equivalent to the where in the sql update statement and is used to filter out the records that need to be updated

Transaction operation

Transaction operations can be easily implemented using the context manager. If an exception occurs, it will be rolled back.

With dataset.connect () as tx: tx ['user'] .insert (dict (name='John Doe', age=46, country='China')) # is equivalent to: db = dataset.connect () db.begin () try: db [' user'] .insert (dict (name='John Doe', age=46) Country='China') db.commit () except: db.rollback () # can also be nested: db = dataset.connect () with db as tx1: tx1 ['user'] .insert (dict (name='John Doe', age=46, country='China')) with db as tx2: tx2 [' user'] .insert (dict (name='Jane Doe', age=37, country='France', gender='female'))

Get data from a table

Users = db ['user'] .all () for user in db [' user']: # print (user ['age']) # chinese_users = user.find (country='China') john = user.find_one (name='John Doe')

Get non-duplicated data

Db ['user'] .clients (' country')

Delete record

Table.delete (place='Berlin')

Execute SQL statement

Result = db.query ('SELECT country, COUNT (*) c FROM user GROUP BY country') for row in result: print (row [' country'], row ['c'])

Export data

Result = db ['users'] .all () dataset.freeze (result, format='json', filename='users.json')

JSON

JSON (JavaScript Object Notation) is a lightweight data exchange format that is very easy for people to read and write.

Import json

Json.dumps encodes Python objects into JSON strings

Json.loads decodes the encoded JSON string into a Python object

MySQL database:

Classification table-categories, including categories web,reversing,crypto (encryption and decryption), mic, etc.

Item list-tasks, including title id, title, flag, score, file & address, topic level, topic detailed description

Flag table-flag, including topic id, user id, score, timestamp

User table-users, including user id, user name, password

Topic classification table-cat_task, including topic id, topic category id

Each piece of data in the flag table is jointly confirmed by the title ID task_id and the user ID user_id, so the compound primary key: primary key (task_id,user_id) is used.

The difference between joint primary key and compound primary key

Python decorator

Decorator inserts extra logic by returning the wrapper object to make an indirect call

Https://www.zhihu.com/question/26930016

Wraps itself is also a decorator, which can copy the meta-information of the original function to the decorator function, which makes the decorator function have the same meta-information as the original function.

From functools import wraps def logged (func): @ wraps (func) def with_logging (* args,**kwargs): print func.__name__ + "was called" return func (* args,**kwargs) return with_logging @ logged def f (x): "does some math"return x + x * x print f.roomnameplate _ # prints' print f.roomdocking _ # prints' does some math'

Web framework adopts flask

From flask import Flask

Flask class is introduced, and Flask class implements a WSGI (Web Server Gateway Interface) application.

App = Flask (_ _ name__)

App is an instance of Flask, which takes the name of a package or module as a parameter, but usually passes _ _ name__

@ app.route ('/') def hello_world (): return 'Hello Worldwide'

Using the app.route decorator saves the relationship between the URL and the executed view function to the app.url_map property. The program that handles the relationship between URL and view function is routing, and the view function here is hello_world.

If _ _ name__ = ='_ main__': app.run (host='0.0.0.0',port=9000)

Using this judgment ensures that the code in this judgment will not be executed when other files reference the file (for example, from hello import app), that is, the app.run function will not be executed.

Execute app.run to start the service. The default Flask only listens on the local 127.0.0.1 address of the virtual machine, with port 5000. The port forwarding port we do to the virtual machine is 9000, so we need to set the host and port parameters. 0.0.0.0 means listening on all addresses so that they can be accessed locally.

After the server starts, werkzeug.serving.run_simple is called to enter polling. By default, single-process, single-threaded werkzeug.serving.BaseWSGIServer is used to process the request. In fact, the standard library BaseHTTPServer.HTTPServer is used to poll while TRUE events for 0.5 seconds through select.select. When we visit http://127.0.0.1:9000/, and find the registered / URL pattern through app.url_map, we find the corresponding hello_world function execution and return hello worldview with a status code of 200. If you access a path that does not exist, such as accessing http://127.0.0.1:9000/a,Flask and cannot find the corresponding mode, it will return Not Found to the browser with a status code of 404.

The role of jsonify in flask

In fact, the function of jsonify is to serialize the data in the form of json into a json string as the body of the response, and set the Content-Type of the response to application/json, and construct the response to return to the client.

The effect is equal to json.dumps

The Content-Type field value of jsonify is application/json

The Content-Type field value of json.dumps is text/html

Modify static folders in flask

To modify the default static folder of flask, you only need to set the static_folder and static_url_path parameters to an empty string when creating the Flask instance.

App = Flask (_ _ name__, static_folder= ", static_url_path=")

Use the url_for function when accessing. The res folder and static folder are at the same level:

Url_for ('static', filename='res/favicon.ico')

Werkzeug

Werkzeug is a WSGI toolkit that can be used as a bottom-level library for an Web framework. It encapsulates a lot of Web framework things, such as Request,Response and so on. Flask framework is developed on the basis of Werkzeug.

Generate_password_hash (password)

Encrypt the plaintext password entered by the user into ciphertext for storage

Password plus salt hash function. It is used to encrypt the plaintext password and return the encrypted ciphertext for user registration.

Function definition:

Werkzeug.security.generate_password_hash (password, method='pbkdf2:sha1', salt_length=8)

Ciphertext format: method$salt$hash

Password: clear text password

Method: hash method (which needs to be supported by the hashlib library) in the format of

Pbpdf2: [: iterations] . Parameter description:

Method: hash method, usually SHA1

Iterations: (optional) number of iterations. Default is 1000.

Slat_length: length of salt value. Default is 8.

Check_password_hash (hash,password)

Verify the password hashed by generate_password_hash, and compare plaintext with ciphertext to see if it is consistent, which is used to verify user login.

Function definition:

Werkzeug.security.check_password_hash (pwhash, password)

Pwhash: hash string generated by generate_password_hash

Password: plaintext password to be verified

Session in flask

Rom flask import session user = db ['users']. Find_one (username=username) session [' user_id'] = user ['id']

Because session is used, you need to set up a secret_key to do hash for some modules.

What is in the Flask Web Development:

The SECRET_KEY configuration variable is a common key that can be used in Flask and multiple third-party extensions. As the name suggests, the strength of encryption depends on the machine density of the variable value. Different programs use different keys, and make sure that others don't know the string you're using.

The main function of SECRET_KEY is to provide a value to do various HASH, which is used as a parameter of the algorithm (salt or other) in its encryption process. So the complexity of this value also affects the complexity of data transmission and storage.

Flask variable rule

To add a variable section to URL, you can mark these special fields as, and this part will be passed to your function as a named parameter. Rules can be used to specify an optional converter

Route ('/ hello/') def index (name): return 'Hello {{name}}!'

Database query

For data queries on dataset, use colons to pass parameters for variables.

Select f.task_id from flags f where f.user_id =: user_id "', user_id=session ['user_id'])

Template rendering

Use the render_template method to render the template. Pass the template name and the parameters you want as keywords to the variables of the template

MySQL

IFNULL (expr1,expr2)

If expr1 is not NULL,IFNULL () returns expr1, otherwise it returns expr2.

IFNULL () returns a number or string value, depending on the context in which it is used.

The max function is used to find the record of the * value in the recordset.

For left join, no matter what condition on is followed by, all the data in the left table is found, so if you want to filter, you need to put the condition after where.

For inner join, only the data of the condition table after on can be found, which can play a filtering role. You can also put the condition after where.

When using left jion, the differences between on and where conditions are as follows:

The on condition is the condition used when generating the temporary table, and it returns the record in the left table regardless of whether the condition in on is true or not.

The where condition is the condition that the temporary table is filtered after the temporary table is generated. At this point, there is no meaning of left join (the record of the table on the left must be returned). If the condition is not true, it will be filtered out.

The usage of order by

Using order by is generally used to sort according to a column (or multiple columns) attribute of the query result (ascending: ASC; descending: DESC; defaults to ascending).

When a sorted column contains a null value:

ASC: the display of tuples sorted as null values.

DESC: the display of tuples sorted as null values.

You can think of null values as infinity.

Select * from s order by sno desc, sage asc

The usage of group by

Group by is grouped according to a column (or more columns) in the query result set, and those with equal values are grouped as a group

1. Refine the object of the set function (count,sum,avg,max,min):

The query results are not grouped, and the set function acts on the entire query result.

After grouping the query results, the set function acts on each group respectively.

SELECT cno,count (sno) from sc group by cno

2. The object of the GROUP BY clause is the intermediate result table of the query.

Grouping method: grouping according to the specified one or more column values, and those with equal values are a group.

After using the GROUP BY clause, only grouping attributes (such as: sno) and set functions (such as: count ()) can appear in the list of column names of the SELECT clause.

Select sno,count (cno) from sc group by sno

3. Grouping multiple column attributes

Select cno,grade,count (cno) from sc group by cno,grade

4. Use HAVING phrases to filter the final output

Only groups that meet the criteria specified by the HAVING phrase are output.

The difference between the HAVING phrase and the WHERE clause: the object of action is different.

1. The WHERE clause acts on the base table or view to select the tuple that meets the condition.

2. The HAVING phrase acts on the group and selects the group that meets the condition.

Select sno from sc group by sno having count (cno) > 3

Select sno,count (cno) from sc where grade > 60 group by sno having count (cno) > 3

Left connection, right connection and equivalent connection of MySQL

1. Left connection (left join)

Select m.columnname... , n. * columnname... .. From left_table m left join right_table n on m.columnname_join=n.columnname_join and n.columnname=xxx where m.columnname=xxx... ..

ON is a join condition that joins equivalent records in table 2 together, but does not affect the number of recordsets. If a record in the table left_table cannot be found in the table right _ table, the record is still displayed in the recordset, except that the table right_table needs to replace the value of the column displayed in the query with NULL

The table n.columnname=xxx in the ON connection condition is used to control whether the right_ table has column values that meet the requirements or whether it is displayed in the query column by NULL replacement, without affecting the number of recordsets.

WHERE sentence controls whether the record meets the query requirements. If it does not, it is filtered out.

two。 Right connection (right join)

Select m.columnname... , n. * columnname... .. From left_table m right join right_table n on m. Columnname_join=n. Columnname_join and M. columnname=xxx where n.columnname=xxx... ..

3. Equivalent connection

Select m.columnname... , n. * columnname... .. From left_table m [inner] join right_table n on m.columnname _ join=n. Columnname_join where m.columnname=xxx... .. And n.columnname=xxx... .

Or

Select m.columnname... , n. * columnname... .. From left_table m, right_table n where m.columnname _ join=n. Columnname_join and m.columnname=xxx... .. And n.columnname=xxx... .

ON is a join condition, which is no longer the same as a left join or a right join. In addition to being a matching condition for records in table 2, it also plays the role of filtering records. If records in left_table cannot find the corresponding records in right_table, they will be filtered out.

The WHERE sentence, whether it involves restrictions on table left_table, table right_table, or conditions involving the join of two tables, will filter the recordset and brush out records that do not meet the requirements.

Jinja2 gets circular index

Jinja2 gets the index of loop {% for i in n%} using loop.index

{% for i in names%} {{loop.index}} / / currently Article x {{i.name}} {% endfor%}

Flask redirects and errors

You can use the redirect () function to redirect the user somewhere else. Drop the request and return the error code, using the abort () function.

From flask import abort, redirect, url_for @ app.route ('/') def index (): return redirect (url_for ('login')) @ app.route (' / login') def login (): abort (401) this_is_never_executed ()

By default, the error code displays a black and white error page. If you want to customize the error page, you can use errorhandler ()

Decorator:

From flask import render_template @ app.errorhandler def page_not_found (error): return render_template ('page_not_found.html'), 404

Notice the 404 after the render_template () call. This tells Flask that the error code for the page is 404, which means it is not found. The default is 200, which means everything is normal.

Flask CSRF protection mechanism

@ app.before_request def csrf_protect (): if request.method = = "POST": token = session.pop ('_ csrf_token' None) if not token or token! = request.form.get ('_ csrf_token'): abort (403) def some_random_string (): return hashlib.sha256 (os.urandom (16). Hexdigest () def generate_csrf_token (): if'_ csrf_token' not in session: session ['_ csrf_token'] = some_random_string () return session ['_ csrf_token']

Note the above function that generates random token in the global variable of flask

App.jinja_env.globals ['csrf_token'] = generate_csrf_token

This is how the template on the web page is introduced.

Flask context processor

The Flask context processor automatically inserts new variables into the context of the template. The context processor runs before the template is rendered, and new values can be inserted in the template context. The context handler is a function that returns a dictionary whose keys are eventually passed into the context of all templates in the application:

@ app.context_processor def inject_user (): return dict (user=g.user)

The above context handler allows the template to use a variable named user value g.user. This example is not very interesting, however, because g is already available in the template, but it explains how the context processor works.

Variables are not limited to values, but context handlers can also make a function available in the template (because Python allows passing functions):

@ app.context_processor def utility_processor (): def format_price (amount, currency=u' employees'): return u' {0pur.2f} {1} .format (amount, currency) return dict (format_price=format_price)

The above context handler makes the format_price function available in all templates:

{{format_price (0.33)}}

Log record

Handler = logging.FileHandler ('flask.log', encoding='UTF-8')

1. Set requestId and log before request

Before each URL request, define requestId and bind to g

@ app.before_request def before_request (): g.requestId = gen_requestId () logger.info ("Start Once Access, and this requestId is% s"% g.requestId)

2. Add a response header and log after the request

In each returned data, put a response header, including the API version and the requestId of this request, and allow all domains to access API across domains, and record the access log.

@ app.after_request def add_header (response): response.headers ["X-SaintIC-Media-Type"] = "saintic.v1" response.headers ["X-SaintIC-Request-Id"] = g.requestId response.headers ["Access-Control-Allow-Origin"] = "*" logger.info (json.dumps ({"AccessLog": {"status_code": response.status_code, "method": request.method, "ip": request.headers.get ('Xmurmuri Ip') Request.remote_addr), "url": request.url, "referer": request.headers.get ('Referer'), "agent": request.headers.get ("User-Agent"), "requestId": str (g.requestId),}}) return response

The basicConfig method can meet your needs in most scenarios, but basicConfig has a big drawback. Calling basicConfig actually adds a handler (FileHandler) to root logger, so that when your program works with other third-party modules that use logging, it will affect the logger behavior of third-party modules. This is determined by the inheritance feature of logger

Logging.basicConfig (level=logging.DEBUG, format='% (asctime) s% (levelname) s% (message) slots, datefmt='%a,% d% b% Y% HGV% MVO% levelname, filename='logs/pro.log', filemode='w') logging.debug ('dddddddddd')

MySQL character coding

In addition to setting up the database, because the character set of the database and table created by dataset by default is not utf8, you need to set it yourself, otherwise it will garbled in Chinese, so you need to modify the character set of the table.

My.cnf [client] default-character-set=utf8 [mysqld] character-set-server=utf8 collation-server=utf8_general_ci default-storage-engine=INNODB

Character set of the table

Show create table tasks; alter table tasks convert to character set utf8; on how to use SQLAlchemy in Dataset to share here, I hope the above content can be of some help to you, can learn more knowledge. If you think the article is good, you can share it for more people to see.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.