Shulou(Shulou.com)06/01 Report--

This article mainly introduces the implementation of python SQL statement how to achieve, has a certain reference value, interested friends can refer to, I hope you can learn a lot after reading this article, the following let Xiaobian take you to understand.

Install pip3 install directsql Import

Directsql currently provides only three external classes

_ _ all__= ["SqlGenerator", "MysqlConnection", "MysqlPool"]

Import mode

From directsql.sqlgenerator import SqlGenerator # this class is used to generate sql statements # below is a pooled connection object MysqlPool and a simple connection object MysqlConnectorfrom directsql.connector import MysqlConnection,MysqlConnector

1 create connection # 1. Passing the named parameter conn= MysqlConnection (host='127.0.0.1', port=3306, password='123456', database='test_base') print (conn.database) conn=MysqlPool (host='127.0.0.1', port=3306, password='123456', database='test_base') # can also use the direct parameter dictionary conn_args = {'host':' 127.0.0.1 parameters, 'port': 3306 'password': '123456,' database':'test_base' } conn = MysqlConnection (* * conn_args) # single connection print (conn.database) conn = MysqlPool (* * conn_args) # pooled connection object print (conn.database) # 2 directly use the string # the following strings are commonly used terminal connection commands string_arg= "mysql-uroot-h227.0.0.1-P3306-p123456-Dtest_base" Conn = MysqlConnection (string_arg=string_arg) print (conn.database) conn = MysqlPool (string_arg=string_arg) print (conn.database) 2 execute sql statement

In fact, directsql encapsulates many statements. Can meet a large part of the daily use scenarios. But if you have complex statements, you still need to call the native sql execution. And many encapsulated methods in directsql are to concatenate sql first and then call the statement, so here is a brief introduction to how directsql executes native sql.

Both the MysqlConnection class and the MysqlPool class execute the sql through the execute_sql method.

For example:

Idnameage1 Luoji 282 Ye Wenjie 2544 Ye Wenjie 27conn = MysqlConnection (string_arg= "mysql-uroot-h227.0.0.1-P3306-p123456-Dtest") result,count=conn.execute_sql ("select * from test_table") print (result) print (count) > ((1, 'Luoji', '28'), (2,' Zhuangyan', '25'), (3,' Ye Wenjie', '54'), (4,' Cheng Xin') '25'), (5,' Yun Tianming', '27') > 5 # it is an ordinary cursor by default You can also specify dictionary cursors: result, count = conn.execute_sql ("select * from test_table", cursor_type='dict') > [{'ID': 1,' name': 'catalogue', 'age':' 28'}, {'ID': 2,' name': 'Zhuangyan', 'age':' 25'}, {'ID': 3,' name':'Ye Wenjie', 'age':' 54'} {'ID': 4,' name': 'Cheng Xin', 'age':' 25'}, {'ID': 5,' name': 'Yun Tianming', 'age':' 27'}] > 5

The execute_sql method returns a tuple, (result set, number of entries)

All the methods shown below return tuples without special instructions and support dict cursors

Attached parameter execution statement

The parameters here are no different from the execute and executemany provided by pymysql. Here are a few simple examples:

# Chuan tuple result,count=conn.execute_sql ("select * from test_table where age=%s", param= (25,)) # pass dictionary result,count=conn.execute_sql ("select * from test_table where age=% (age) s", param= {'age': 25}) # tuple list result,count=conn.execute_sql ("insert into test_table (`age`, `name`) values (% s)", param= [(' Song Yunhui', 37), ('Cheng Kaiyan') 33)]) # Dictionary list result, count = conn.execute_sql ("insert into test_table (`age`, `name`) values (% (age) s)% (name) s)", param= [{"name": "Song Yunhui", 'age':37}, {"name": "Cheng Kaiyan",' age':33}]) 3 select method

The select method can accept multiple parameters, and the list of parameters is as follows.

Def select (self, columns='id', table=None, where=None, group_by: str = None, order_by: str = None, limit: int = None, offset=None,cursor_type=None):

"" conn.select ('*', 'test_table')

Select id from test_table where age=25

"" conn.select ('*', 'test_table', where= {' age': 25})

Select name,age from test_table where age=25 and id=2

Multiple fields are passed directly into the string

"" conn.select ("age,name", 'test_table', where= {' age': 25)

Incoming list / tuple

"" conn.select (['age','name'],' test_table', where= {'age': 25)

Select * from test_table group by id order by age desc limit 1 offset 1

"" conn.select ('*', 'test_table', order_by='age desc',group_by='id',limit=1,offset=1)

The select function doesn't even look as fast as writing native sql directly, but if the query conditions are constantly changing, especially the where conditions, then using the select method is more convenient than self-stitching.

For example, you need to constantly read a dictionary variable and then query the data according to the conditions in that variable, and the number of keys in this dictionary will change, but the keys happen to be the fields of the table. It is easy to use the select method at this time, as long as you make the where parameter equal to that dictionary.

To be fair, this method is of little use.

4 insert_into method def insert_into (self, table, data: dict or list, columns=None, ignroe=False, on_duplicate_key_update: str = None, return_id=False):

This method can accept incoming dictionaries or dictionary lists, and optionally returns the number of entries affected by the cursor or the id of the newly inserted data.

When columns is empty, all the keys of the first data will be fetched, so make sure that all the data keys are the same.

# incoming dictionary data_1 = {"age": 44, 'name': "Lei Dongbao} count = conn.insert_into (' test_table', data_1) # default returns the number of affected entries print (count) # > > 1 return_id= conn.insert_into ('test_table', data_1,return_id=True) # optional return idprint (return_id) > 22533 # incoming dictionary list data_2= {" age ": 22 'name': "Song Yunping} all_data= [data _ 1 data data 2] count = conn.insert_into (' test_table', all_data) # limits the fields to be inserted. (the dictionary has multiple fields But when you only need to insert the specified fields into the table) data_3= {"age": 44, 'name': "Lei Dongbao," title ":" Village Branch Book "} # title is not needed, as long as age and namecount = conn.insert_into (' test_table', data_1,columns= [" age "," name "]) # ignore parameter data_1 = {" age ": 44, 'name':" Lei Dongbao " "id": 22539} count = conn.insert_into ('test_table',ignore=True) print (count) > 0 # because id 22539 already exists in the table This record will not be inserted, affecting 0 data # on_duplicate_key_update parameter data_1 = {"age": 44, 'name': "Lei Dongbao", "id": 22539} # id=22539 already exists count = conn.insert_into (' test_table', data_1,on_duplicate_key_update=' name= "copy"') print (count) # returns the number of influence entries > 2 # attempts to insert one, but duplicates occur So delete the new data and update the old data. Actually affected two.

The on_duplicate_key_update parameter is provided in the insert_into method, but it is actually quite fancy to use, and you need to concatenate the statements after passing on_duplicate_key_update.

If you just need to update a specific field of the old data to the value of the corresponding field of the new data in case of repetition. The merge_into method is more suitable.

5 merge_into method

The syntax of merge into is provided in other relational databases, but not in mysql. But here we encapsulate a merge_into-like method through insert and on_duplicate_key_update syntax. This method returns the number of affected entries.

Def* merge_into (self, table, data, columns=None, need_merge_columns: list = None):

When columns is empty, all the keys of the first data will be fetched, so make sure that all the data keys are the same.

Need_merge_columns is the field that needs to be replaced (overwritten) in the event of a repetition.

Data_1 = {"age": 44, 'name': "Lei Dongbao", "id": 22539} data_2= {"age": 22,' name': "Song Yunping", "id": 22540} all_data = [data_1, data_2,] count=conn.merge_into ('test_table',all_data,need_merge_columns= [' name',]) print (count) > 4 # both data happen to be duplicated Insert two entries and then delete them and modify them to return the 46 replace_into method

The method is simple and does not explain too much. This method returns the number of affected entries.

Def replace_into (self,table, data: dict or list, columns=None)

Data_1 = {"age": 44, 'name': "Lei Dongbao", "id": 22539} data_2= {"age": 22,' name': "Song Yunping", "id": 22540} all_data = [data_1, data_2,] count=conn.replace_into ('test_table',all_data) 7 update method

Def update (self,table, data: dict, where, columns: None or list = None, limit=None):

The method data parameter accepts only incoming dictionaries. This method returns the number of affected entries.

Data_1 = {"age": 44, 'name': "Ray copy"} count=conn.update (' test_table',data_1,where= {'id':22539}) # Update the data of id=22539 to the new data_1print (count) > 1

In addition, a derivative method is provided.

Def update_by_primary (self, table, data: dict, pri_value, columns=None, primary: str = 'id'):

Used to update data through the primary key. Pri_value is the value of the primary key. Primary is the primary key, default is id

Data_1 = {"age": 44, 'name': "Ray cpy"} count=conn.update_by_primary (' test_table',data_1,pri_value=22539) 8 delete method def delete_by_primary (self,table, pri_value, primary='id'): "" delete data through primary key "" def delete (self,table, where: str or dict Limit: int = 0): "" Delete data through where condition "" count=conn.delete ('test_table',where= {' name':' Lei Dongbao'})) # Delete data of name= Redongbao count=conn.delete_by_primary ('test_table',pri_value=22539) # Delete data with primary key equal to 22539 9 use transaction

Def do_transaction (self, sql_params: list, cursor_type=None):

Sql_params is a list of tuples. [(sql_1,param_1), (sql_2,param_2]

Input None if sql does not need parameters, such as [(sql_1,None),]

Sql_params = [("update test_table set name=% (name) s where id=% (id) s", {'name':' Loki', 'id': 22539}), ("update test_table set name=% (name) s where id=% (id) s", {' name': 'mask',' id': 22540}),] count=conn.do_transaction (sql_params) > (() 1) # returns the result of the last execution statement and affects the number of 10 entries to read the streaming cursor result

Def read_ss_result (self, sql, param=None, cursor_type='ss'):

Cursor_type optional ss and ssdict

Note that this method returns a generator object, and the result needs to be iterated over and over again.

Result=conn.read_ss_result ("select * from test_table") for data in result: print (data) Thank you for reading this article carefully. I hope the article "how to implement SQL sentences in python" shared by the editor will be helpful to you. At the same time, I hope you will support us and pay attention to the industry information channel. More related knowledge is waiting for you to learn!

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.