How to solve the error when running mysql statement in python

1 python reports an error when running mysql statement

As we all know, python has a translation mechanism. %s and %d will be translated into strings or numbers, and fuzzy queries of sql also need to be used. By %, when all fuzzy queries are performed, it would be very embarrassing if the query condition happens to be a variable.

The solution is actually very simple, just take out the strings that need to be fuzzy queried from the sql and splice them together

Wrong way

<code>shopId = "zbw_010002"</code><code><br></code><code>'''select * from base_saleflows where shopId='{0}' and card_id is not NULL and standard_cls4 like "%湿巾%" '''.format(shopId)</code>

Found that it was wrong, mysql cannot run such a statement.

<code>args='%湿巾%'</code><code>shopId = "zbw_010002"</code><code>mysql_sentence='''select a.shopId, a.sale_money,a.card_id ,a.standard_cls4 from base_saleflows a join  base_vips b on a.card_id = b.card_id where a.shopId='{0}' and a.card_id is not NULL and a.standard_cls4 like '{1}' '''.format(shopId,args)</code><code>print(mysql_sentence)</code><code><br></code>

The result is

select * from base_saleflows a join  base_vips b on a.card_id = b.card_id where a.shopId='zbw_010002' and a.card_id is not NULL and a.standard_cls4 like '%湿巾%'

2 String grouping and splicing

Group the cls3 column according to the serial number flow_no and splice strings. The splicing symbol is '-'

#  分组拼接result = vipsaleflow_common.pivot_table(values='standard_cls3',index='flow_no',aggfunc=lambda x:x.str.cat(sep='-'))

##Application: Group each

quarter# according to ( year , quarter , branch number , wet wipes type) ##Number of new customers

四 Derived based on timestamp year, month and quarter

saleflow['oper_date']=saleflow['oper_date'].astype(str)  #字符串saleflow['oper_date'] = saleflow.oper_date.apply(lambda x:datetime.strptime(x, '%Y-%m-%d %H:%M:%S'))saleflow["month"] = saleflow.oper_date.map(lambda x: x.month)saleflow["year"] = saleflow.oper_date.map(lambda x: x.year)#https://www.it1352.com/584941.htmllookup = {1: 1, 2: 1,3: 1,4: 2, 5: 2, 6: 2, 7: 3,8: 3,9: 3,10: 4, 11: 4,12: 4}saleflow['Season'] = saleflow['oper_date'].apply(lambda x: lookup[x.month]) saleflow['YearMonth'] = saleflow['oper_date'].map(lambda x: 100*x.year + x.month)

The above is the detailed content of How to solve the error when running mysql statement in python. For more information, please follow other related articles on the PHP Chinese website!