pymysql modular

install

pip3 install pymysql

link , perform sql, close ( The cursor )

import pymysql
user= input(' user name :>>').strip()
pwd= input(' password :>>').strip() # First link , Get the cursor
conn=pymysql.connect(host='localhost',user='root',password='',
database='day47',charset='utf8')
cursor=conn.cursor() # Get the cursor , namely mysql >
# perform sql
sql='select * from user where user="%s" and password="%s";'%(user,pwd)
print(sql) # Be careful %s Need double quotes
rows = cursor.execute(sql) # Get the number of rows affected cursor.close()
conn.close() if rows:
print(' Login successful ')
else:
print(' Login failed ')

execute() And sql Inject

principle

Symbol -- It'll be annotated after sql, The right grammar :-- At least one arbitrary character after

The phenomenon

 The last space , In one sql If you encounter select *
from t1 where id > 3 -- and name='egon'; be -- The conditions after that have been commented out #1、sql Injected : Users exist , password bypass
egon' -- Any character #2、sql Injected : The user doesn't exist , Bypass users and passwords
xxx' or 1=1 -- Any character

Solution  

# It turns out that we are right sql String splicing 
# sql="select * from userinfo where name='%s' and password='%s'" %(user,pwd)
# print(sql)
# rows=cursor.execute(sql) # to (execute Do string splicing for us , We don't need and must not do it any more %s In quotation marks )
sql="select * from userinfo where name=%s and password=%s" # Be careful %s You need to get rid of the quotation marks , because pymysql It will automatically add
rows=cursor.execute(sql,[user,pwd]) #pymysql The module automatically helps us solve sql Injection problem , As long as we follow pymysql The rules of .

increase 、 Batch increase Delete 、 Change :conn.commit()

increase

import pymysql
First link , Get the cursor
conn=pymysql.connect(host='localhost',user='root',password='',database='day47')
cursor=conn.cursor() # Get the cursor , namely mysql >
# perform sql increase :
sql='insert into user1(user,password) VALUES (%s,%s)'
print(sql)
# rows = cursor.execute(sql,('xixi',123)) # Insert a record
rows = cursor.executemany(sql,[('xixi',123),('aaa',456),('ttt',147)]) # Insert multiple rows of records
print('%s row in set (0.00 sec)'%rows) conn.commit() # Commit to database
cursor.close()
conn.close()

Batch increase

# coding:utf-8
import pymysql # Open database connection
db = pymysql.connect(host='localhost', port=3306,
user='username', passwd='password', db='database_name', charset='utf8') # Use cursor() Method get operation cursor
cursor = db.cursor() # SQL Insert statement
sql = "INSERT INTO EMPLOYEE(FIRST_NAME, AGE, SEX) VALUES (%s,%s,%s)"
# One tuple perhaps list
T = (('xiaoming', 31, 'boy'), ('hong', 22, 'girl'), ('wang', 90, 'man')) try:
# perform sql sentence
cursor.executemany(sql, T)
# Commit to database execution
db.commit()
except :
# Roll back if an error occurs
db.rollback()
# Close cursor
cursor.close()
# Close database connection
db.close()

Delete

import pymysql
# First link , Get the cursor
name=input('>>').strip()
conn=pymysql.connect(host='localhost',user='root',password='',database='day47')
cursor=conn.cursor() # Get the cursor , namely mysql >
# perform sql Delete :
sql='delete from user1 where user =%s;' # Delete data
print(sql)
rows = cursor.execute(sql,(name))
print('%s row in set (0.00 sec)'%rows) conn.commit() # Commit to database
cursor.close()
conn.close()

Change

import pymysql
# First link , Get the cursor
id=input('>>').strip()
conn=pymysql.connect(host='localhost',user='root',password='',database='day47')
cursor=conn.cursor() # Get the cursor , namely mysql >
# perform sql Change :
sql=' update user1 set password = "5555555" where id=%s;'
print(sql)
rows = cursor.execute(sql,(id))
print('%s row in set (0.00 sec)'%rows) conn.commit() # Commit to database
cursor.close()
conn.close()

check :fetchone,fetchmany,fetchall

# --------- check fetchone,fetchmany,fetchall-----------
import pymysql
conn=pymysql.connect(host='localhost',user='root',password='',database='day47')
cursor=conn.cursor() # Get the cursor , namely mysql >
# perform sql check :
sql='select * from user1;'
rows = cursor.execute(sql) # Check the list fetchone
res1=cursor.fetchone()
res2=cursor.fetchone()
res3=cursor.fetchone()
print(res1)
print(res2)
print(res3)
print(res3[0]) # Check more than one fetchmany
print(cursor.fetchmany(3))
print(cursor.fetchone()) # Check all fetchall
print(cursor.fetchall())
print(cursor.fetchone()) #------- Cursor movement --------
#1. Absolute path : From the beginning of the file
print(cursor.fetchall())
cursor.scroll(1,mode='absolute')
print(cursor.fetchone())
cursor.scroll(3,mode='absolute')
print(cursor.fetchone()) #2. Relative paths :
print(cursor.fetchone())
print(cursor.fetchone())
cursor.scroll(2,mode='relative') # Move two back relative to the top two
print(cursor.fetchone()) print('%s row in set (0.00 sec)' %rows)
cursor.close()
conn.close()

Get the auto increment of the last inserted data ID

------ Look at... In the last row of the table iD
import pymysql
conn=pymysql.connect(host='localhost',user='root',password='',
database='day47',charset='utf8')
cursor=conn.cursor() sql='insert into user1(user,password) values(%s,%s);'
rows=cursor.execute(sql,('alex',''))
# rows=cursor.executemany(sql,[('yuanhao','123'),('laowu','123'),('kgf','12323')])
conn.commit()
print(cursor.lastrowid) # Look at... In the last row of the table iD cursor.close()
conn.close()

Asynchronous processing

# use twisted The library inserts data into the database asynchronously
import pymysql
from twisted.enterprise import adbapi
from twisted.internet import reactor class MysqlTwistedPipeline(object):
def __init__(self, dbpool):
self.dbpool = dbpool @classmethod
def from_settings(cls, settings):
# Need to be in setting Set database configuration parameters in
dbparms = dict(
host=settings['MYSQL_HOST'],
db=settings['MYSQL_DBNAME'],
user=settings['MYSQL_USER'],
passwd=settings['MYSQL_PASSWORD'],
charset='utf8',
cursorclass=pymysql.cursors.DictCursor,
use_unicode=True,
)
# Connect ConnectionPool( Use MySQLdb Connect , perhaps pymysql)
dbpool = adbapi.ConnectionPool("MySQLdb", **dbparms) # ** Let the parameter become a variable parameter
return cls(dbpool) # Returns the instanced object def process_item(self, item, spider):
# Use twisted take MySQL The insert becomes asynchronous
query = self.dbpool.runInteraction(self.do_insert, item)
# Add exception handling
query.addCallback(self.handle_error) def handle_error(self, failure):
# Handle exceptions when inserting asynchronously
print(failure) def do_insert(self, cursor, item):
# Perform a specific insert
insert_sql = """
insert into jobbole_artitle(name, base_url, date, comment)
VALUES (%s, %s, %s, %s)
"""
cursor.execute(insert_sql, (item['name'], item['base_url'], item['date'], item['coment'],))

5.15 pymysql More articles on modules

  1. Python In the operation mysql Of pymysql Module details

    Python In the operation mysql Of pymysql Module details Preface pymsql yes Python In the operation MySQL Module , How to use it and MySQLdb Almost the same . But at the moment, pymysql Support python3.x The latter does not support ...

  2. python The first day of actual combat -pymysql Module and practice

    operating system Ubuntu 15.10 IDE & editor JetBrains PyCharm 5.0.2 ipython3 Python edition python-3.4.3 install pymysql model ...

  3. python Storage engine mysql( library , surface , That's ok ) Single table multi table operation (foreign key) sql_mode pymysql Module explanation

    ################## summary ############### mysql Common data types integer :tinyint  int(42 About 100 million )  bigint decimal :float double dec ...

  4. ( turn )Python In the operation mysql Of pymysql Module details

    original text :https://www.cnblogs.com/wt11/p/6141225.html https://shockerli.net/post/python3-pymysql/----Python ...

  5. python The whole development of the stack ,Day63( Subquery ,MySQl Create users and authorizations , Visualization tools Navicat Use ,pymysql Use of modules )

    Yesterday's review There are three kinds of relations between variants of foreign bonds : For one more : There are many in the left table Right table one establish The one on the left More about the right table Don't set up foreign key( From table id) refreences The main table of (id) Many to many Create a third table (f ...

  6. Chapter viii. | 3. MyAQL database |Navicat Tools and pymysql modular | The built-in function | Index principle

    1.Navicat Tools and pymysql modular Operating in a production environment MySQL Database is still recommended to use the command line tool mysql, But when we develop our own tests , You can use visualization tools Navicat, Operate in the form of a graphical interface MySQL Count ...

  7. mysql python pymysql modular Basic use

    We all passed MySQL Comes with the command line client tool mysql To manipulate the database , How is that python How to operate the database in the program ? And that's where it comes in pymysql modular , This module is essentially a socket client software , It needs to be installed before use pi ...

  8. 05 Introduction to database - Regular expressions 、 User management 、pymysql modular

    One . Regular expressions Regular expressions are used for fuzzy queries , Fuzzy query has been mentioned  like Support only % and _ Far less flexible than regular expressions, of course, most of the time like Sufficient use # grammar select *from table whe ...

  9. pymysql Module USES ---Python Connect MySQL database

    pymysql Module USES ---Python Connect MySQL database Browse the catalog pymysql Introduce Connect to database execute( ) And sql Inject Add, delete, change and check operation Advanced usage One .pymysql Introduce 1. Introduce ...

Random recommendation

  1. less Study - Browser side compilation ( One )

    demo Address  http://www.qq210.com/shoutu/android 1. download less package , Official website 2. introduce less file <link rel="stylesheet/les ...

  2. java Get dynamic web Application deployment path

    public static String DEPLOY_PATH = null; static { String CurrentClassFilePath = Constant.class.getRe ...

  3. URL Pass on + Solution to the problem of changing space number to background

    It's a coincidence that we have this problem today , Here's the solution we found online . original text :http://blog.sina.com.cn/s/blog_a0949eec01010xta.html Today, when debugging the client passing parameters to the server , Parameters ...

  4. Django_ Implement paging

    demand : There's a lot of data for , I don't want to show it all on one page at a time , Need a paged , Set the content of each page that , How to meet this demand ? Through the third party module  django-pure-pagination pip ins ...

  5. mysql(mariadb) Master slave configuration

    Environmental Science : Kernel version :uname -r System version :cat /etc/redhat-release

  6. Mac Lower installation zsh(Oh My ZSH) Of shell, Replace the original bash

    explain : In the beginning zsh I refused , Because it's easy to install , It's hard to unload , And installed after the default Shell The configuration file for cannot be used , such as ~/.bashrc these . So think twice before you install it ! Official website :http://ohmyz.s ...

  7. IOS When wechat is running in the background, the countdown is suspended

    link :https://pan.baidu.com/s/1i7cSkqL password :g80i Recently, I made a H5 Answer game , But there's a terminal problem with the countdown : Press on the phone Home Key to put wechat in the background ,IOS11 Meeting ...

  8. ARM kernel

    ARM Related knowledge : ARM nucleus :A8,ARM11,ARM9 Instruction Architecture :ARMv7,ARMv6,ARMv4 ARM Nuclear power is divided into two camps : Classic :ARM7,ARM9,ARM11 Cortex: Cortex A: ...

  9. About Oracle to_char() Function IW,WW Week shows

    1)ww The algorithm for each year 1 month 1 Day is the first week ,date+6 For the end of each week for example 20050101 It's the first day of the first week , And the last day of the first week is 20050101+6=20050107 The formula The first day of the week :date + ...

  10. About change placeholder The color of the

    input::-webkit-input-placeholder{ color:red; } input::-moz-placeholder{ /* Mozilla Firefox 19+ */ co ...