洗礼灵魂,修炼python(91卡塔尔-- 知识拾遗篇 —

作者:编程技术

首先你得学会基本的mysql操作语句:mysql学习

附带,python要想操作mysql,靠python的嵌入模块是那多少个的,而假设通过os模块调用cmd命令就算原理上是可以的,不过依旧不太平价,那么那一个主题材料,很已经有人想过了,并且还做出来了,不赘述,就是第三方模块pymysql和mysqldb了。

但是,python3暂不协理mysqldb,pymysql却是python2和3都支持的

因此,本篇博文说说pymysql,学了pymysql,mysqldb其实你也会了

 

Python开发【模块】:PyMySQL,python开发pymysql

增:

 1卡塔 尔(英语:State of Qatar)固定数据插入:

#!usr/bin/env python
#-*- coding:utf-8 -*-
# author:yangva

import pymysql
# 创建数据库连接
conn = pymysql.connect(host='127.0.0.1',port=3306,user='root',passwd='',db='test')

# 创建游标,这里的游标你可以理解为文本操作里的walk指针
cursor = conn.cursor()
# sql语句
cursor.execute('insert into user(name,age,part_id) VALUES("ling",32,2)')
# 提交数据,你可以理解为文本操作里的flush()刷新缓存存储为数据
conn.commit()

# 关闭连接
cursor.close()
conn.close()

  

在运作在此以前,先看下数据Curry的数据:

分分快三计划 1

 

好的,起初运维:

运行结果:

分分快三计划 2

 

不曾报错就是好新闻,再看下数占领转换并未有:

分分快三计划 3

 

好的,插入进去了

 

先看图:

 

分分快三计划 4

 

实则你有未有开采,那个都以一定的格式,就和socket差不离,就阐明的这两句是有转移的,第一句是三回九转数据库等设置,第二据就是大家的sql语句,那么难点就轻便了对吗?

 

然后,比如你插入的数码希望是汉语的,还足以在首先句创造数据库连接时时加二个参数charset

# 创建数据库连接
conn = pymysql.connect(host='127.0.0.1',port=3306,user='root',passwd='',db='test',charset='utf8')

  

 

2卡塔尔国字符串拼接活动插入:

那正是说再看,假诺我们让客商本人输入待插入的数额吧:

此外不变,只订正那意气风发有的

A:多个列数据插入

分分快三计划 5

 

小心上边包车型客车VALUES()里并不是再加引号,因为input里已经有引号了

 

运维结果:

 分分快三计划 6 

 

分分快三计划 7

 

 

B:如若你只是插入生机勃勃单个数据,能够那样

分分快三计划 8

 

C:那么有对象要问了,为啥不这么作字符串拼接呢:

分分快三计划 9

 

 那个方式尽管也使得,可是是不许的,因为也许会不由自主sql注入漏洞,而用地点的方法的话,execute()方法能够流传七个参数,自动帮我们字符串拼接好了

 

D:sql注入漏洞圭臬:

分分快三计划 10

 

看来了吧,其实笔者的表里并不曾23的部门id对啊,但是照旧查询出来了,那么只要查询的表是保存的有着客商名和密码吗?这一个注入是或不是很骇人听闻了对啊?

 

再有越来越多的流入语句:

万能密码:'or'='or' 

select * from table where username='' or '=' or '' and password=''; 


"or"a"="a     

')or('a'='a

")or("a"="a

'or 1=1--

"or 1=1--

'or"='

'or 1=1\0

'or 1=1/*

admin' or 1=1/*

  

于是通常不要这种字符串拼接的艺术,依然利用pymysql给我们封装好的方法execute和executemany

 

E:插入多少个可迭代对象:

#!usr/bin/env python
#-*- coding:utf-8 -*-
# author:yangva

import pymysql
# 创建数据库连接
conn = pymysql.connect(host='127.0.0.1',port=3306,user='root',passwd='',db='test',charset='utf8')

# 创建游标
cursor = conn.cursor()

user1 = input('请输入待插入的姓名:')
age1 = input('请输入待插入的年龄:')
id1 = input('请输入待插入的部门id:')
print('已记录')

user2 = input('请输入待插入的姓名:')
age2 = input('请输入待插入的年龄:')
id2 = input('请输入待插入的部门id:')
print('已记录')

li = [(user1,age1,id1),
      (user2,age2,id2)
      ]
# sql语句
cursor.executemany('insert into user(name,age,part_id) VALUES(%s,%s,%s)',li)

# 提交数据
conn.commit()

# 关闭连接
cursor.close()
conn.close()

只顾上面作者插入sql语句的主意已经改成executemany,不然使用execute方法会报错

 

运作结果:

分分快三计划 11

 

分分快三计划 12

 

PyMySQL

1、安装

[[email protected] ~]# pip install PyMySQL

 

2、初识

创立数据表结构:

mysql> CREATE TABLE `users` (
    ->     `id` int(11) NOT NULL AUTO_INCREMENT,
    ->     `email` varchar(255) COLLATE utf8_bin NOT NULL,
    ->     `password` varchar(255) COLLATE utf8_bin NOT NULL,
    ->     PRIMARY KEY (`id`)
    -> ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin
    -> AUTO_INCREMENT=1 ;
Query OK, 0 rows affected (0.07 sec)

动用示例:

import pymysql.cursors

# Connect to the database
connection = pymysql.connect(host='192.168.1.134',
                             port=3306,
                             user='remote',
                             password='tx_1234abc',
                             db='Jefrey',
                             charset='utf8mb4',
                             cursorclass=pymysql.cursors.DictCursor)

try:
    with connection.cursor() as cursor:   #增加
        # Create a new record
        sql = "INSERT INTO `users` (`email`, `password`) VALUES (%s, %s)"
        cursor.execute(sql, ('[email protected]', 'very-secret'))

    # connection is not autocommit by default. So you must commit to save your changes.
    connection.commit()   # 提交,不然数据库不生效

    with connection.cursor() as cursor:   #查询
        # Read a single record
        sql = "SELECT `id`, `password` FROM `users` WHERE `email`=%s"
        cursor.execute(sql, ('[email protected]',))
        result = cursor.fetchone()
        print(result)
finally:
    connection.close()

# {'id':1, 'password': 'very-secret'}

 

3、connection对象

Representation of a socket with a mysql server.        与mysql建立socket

The proper way to get an instance of this class is to call connect().

Establish a connection to the MySQL database. Accepts several arguments:

参数:

分分快三计划 13

class pymysql.connections.Connection(host=None, user=None, password='', database=None, port=0, unix_socket=None, charset='', sql_mode=None, read_default_file=None, conv=None, use_unicode=None, client_flag=0, cursorclass=<class 'pymysql.cursors.Cursor'>, init_command=None, connect_timeout=10, ssl=None, read_default_group=None, compress=None, named_pipe=None, no_delay=None, autocommit=False, db=None, passwd=None, local_infile=False, max_allowed_packet=16777216, defer_connect=False, auth_plugin_map={}, read_timeout=None, write_timeout=None, bind_address=None) 

host – Host where the database server is located
•user – Username to log in as
•password – Password to use.
•database – Database to use, None to not use a particular one.
•port – MySQL port to use, default is usually OK. (default: 3306)
•bind_address – When the client has multiple network interfaces, specify the interface from which to connect to the host. Argument can be a hostname or an IP address.
•unix_socket – Optionally, you can use a unix socket rather than TCP/IP.
•charset – Charset you want to use.
•sql_mode – Default SQL_MODE to use.
•read_default_file – Specifies my.cnf file to read these parameters from under the [client] section.
•conv – Conversion dictionary to use instead of the default one. This is used to provide custom marshalling and unmarshaling of types. See converters.
•use_unicode – Whether or not to default to unicode strings. This option defaults to true for Py3k.
•client_flag – Custom flags to send to MySQL. Find potential values in constants.CLIENT.
•cursorclass – Custom cursor class to use.
•init_command – Initial SQL statement to run when connection is established.
•connect_timeout – Timeout before throwing an exception when connecting. (default: 10, min: 1, max: 31536000)
•ssl – A dict of arguments similar to mysql_ssl_set()’s parameters. For now the capath and cipher arguments are not supported.
•read_default_group – Group to read from in the configuration file.
•compress – Not supported
•named_pipe – Not supported
•autocommit – Autocommit mode. None means use server default. (default: False)
•local_infile – Boolean to enable the use of LOAD DATA LOCAL command. (default: False)
•max_allowed_packet – Max size of packet sent to server in bytes. (default: 16MB) Only used to limit size of “LOAD LOCAL INFILE” data packet smaller than default (16KB).
•defer_connect – Don’t explicitly connect on contruction - wait for connect call. (default: False)
•auth_plugin_map – A dict of plugin names to a class that processes that plugin. The class will take the Connection object as the argument to the constructor. The class needs an authenticate method taking an authentication packet as an argument. For the dialog plugin, a prompt(echo, prompt) method can be used (if no authenticate method) for returning a string from the user. (experimental)
•db – Alias for database. (for compatibility to MySQLdb)
•passwd – Alias for password. (for compatibility to MySQLdb)

Parameters

 方法:

autocommit_mode= None 
  specified autocommit mode. None means use server default.
begin() 
  Begin transaction.
close() 
  Send the quit message and close the socket
commit() 
  Commit changes to stable storage
cursor(cursor=None) 
  Create a new cursor to execute queries with
ping(reconnect=True) 
  Check if the server is alive
rollback() 
  Roll back the current transaction
select_db(db) 
  Set current db
show_warnings() 
  SHOW WARNINGS

 

4、cursor对象

① class pymysql.cursors.``Cursor(connection)

This is the object you use to interact with the database.   cursor对象用于数据库交互作用

Do not create an instance of a Cursor yourself. Call connections.Connection.cursor().  不要本身用Cursor生成实例,使用Connection.cursor()

方法:

callproc(procname, args=()) 
Execute stored procedure procname with args
procname – string, name of procedure to execute on server
args – Sequence of parameters to use with procedure
Returns the original args.

close()     关闭
    Closing a cursor just exhausts all remaining data.

execute(query, args=None)  #执行sql语句
    Execute a query  

    Parameters:
        •query (str) – Query to execute.  
        •args (tuple, list or dict) – parameters used with query. (optional)

    Returns:    Number of affected rows   #返回查询到的个数
    Return type:    int
    If args is a list or tuple, %s can be used as a placeholder in the query. If args is a dict, %(name)s can be used as a placeholder in the query.

executemany(query, args) 
    Run several data against one query

    Parameters:
        •query – query to execute on server
        •args – Sequence of sequences or mappings. It is used as parameter.

    Returns:    Number of rows affected, if any.
    This method improves performance on multiple-row INSERT and REPLACE. Otherwise it is equivalent to looping over args with execute().

fetchall() 
    Fetch all the rows

fetchmany(size=None) 
    Fetch several rows

fetchone() 
    Fetch the next row

max_stmt_length= 1024000 
    Max statement size which executemany() generates.
    Max size of allowed statement is max_allowed_packet -       packet_header_size. Default value of max_allowed_packet is 1048576.

mogrify(query, args=None) 
    Returns the exact string that is sent to the database by calling the     execute() method.
    This method follows the extension to the DB API 2.0 followed by Psycopg.

setinputsizes(*args) 
    Does nothing, required by DB API.

setoutputsizes(*args) 
    Does nothing, required by DB API. 

② 其他:

分分快三计划 14

class pymysql.cursors.SSCursor(connection) 
Unbuffered Cursor, mainly useful for queries that return a lot of data, or for connections to remote servers over a slow network.

Instead of copying every row of data into a buffer, this will fetch rows as needed. The upside of this is the client uses much less memory, and rows are returned much faster when traveling over a slow network or if the result set is very big.

There are limitations, though. The MySQL protocol doesn’t support returning the total number of rows, so the only way to tell how many rows there are is to iterate over every row returned. Also, it currently isn’t possible to scroll backwards, as only the current row is held in memory.
fetchall() 
Fetch all, as per MySQLdb. Pretty useless for large queries, as it is buffered. See fetchall_unbuffered(), if you want an unbuffered generator version of this method.
fetchall_unbuffered() 
Fetch all, implemented as a generator, which isn’t to standard, however, it doesn’t make sense to return everything in a list, as that would use ridiculous memory for large result sets.
fetchmany(size=None) 
Fetch many
fetchone() 
Fetch next row
read_next() 
Read next row

class pymysql.cursors.DictCursor(connection) 
A cursor which returns results as a dictionary

class pymysql.cursors.SSDictCursor(connection) 
An unbuffered cursor, which returns results as a dictionary

cursor其余对象

 

5、execute与executemany使用及品质比较

①  execute 试行单条sql语句

源码:

def execute(self, query, args=None):
    """Execute a query
    """
    while self.nextset():
        pass

    query = self.mogrify(query,
                         args)  # sql语句拼接,加上引号 select * from users WHERE email='[email protected]' and password='very-secret' 

    result = self._query(query)  # 最终执行connection的query方法,统计匹配的rows返回
    self._executed = query  # 设置执行过的语句为query,查询的方法会用到
    return result

示例:

# cursor
try:
    with connection.cursor() as cursor:
        # Create a new record
        sql = " select * from users WHERE email=%s and password=%s "
        rows_count = cursor.execute(sql, ('[email protected]', 'very-secret'))
        print(rows_count)
        result = cursor.fetchone()
        print(result)
finally:
   connection.close()

# 12   查不到时为 0
# {'id': 4, 'email': '[email protected]', 'password': 'very-secret'}

②  executemany 试行多条语句

源码:

def executemany(self, query, args):
    # type: (str, list) -> int
    """Run several data against one query """
    if not args:
        return

    m = RE_INSERT_VALUES.match(query)  # 正则匹配,暂忽略
    if m:
        q_prefix = m.group(1) % ()
        q_values = m.group(2).rstrip()
        q_postfix = m.group(3) or ''
        assert q_values[0] == '(' and q_values[-1] == ')'
        return self._do_execute_many(q_prefix, q_values, q_postfix, args,
                                     self.max_stmt_length,
                                     self._get_db().encoding)

    self.rowcount = sum(self.execute(query, arg) for arg in args)  # 重点!还是循环执行execute方法
    return self.rowcount= 1

示例:

# executemany
try:
    with connection.cursor() as cursor:
        # Create a new record
        sql = " select * from users WHERE email=%s and password=%s "
        rows_count = cursor.executemany(sql, [('[email protected]', 'very-secret'),('[email protected]', 'test')])
        print(rows_count)
        result = cursor.fetchone()   # 查到的是后一条语句的第一条,一会儿剖析fitchone方法 fitchall会打印两次查询的所有
        print(result)

finally:
   connection.close()

# 26
# {'id': 16, 'email': '[email protected]', 'password': 'test'}

③ 数据库各插入10000条数据,打字与印刷下进行时间 

双面都共用了叁个措施,以为应该不会差超多(打脸卡塔 尔(英语:State of Qatar)

# 性能对比 环境python3.6
try:
    # execute方法
    with connection.cursor() as cursor:
        # Create a new record
        start = time.time()
        sql = "INSERT INTO `users` (`email`, `password`) VALUES (%s, %s)"
        for i in range(10000):
            rows_count = cursor.execute(sql,('[email protected]','test' str(i)))
        connection.commit()
        print('execut insert 10000 rows cost %s',time.time()-start)

    # executemany方法
    with connection.cursor() as cursor:
        # Create a new record
        start = time.time()
        sql = "INSERT INTO `users` (`email`, `password`) VALUES (%s, %s)"
        # for i in range(10000):
        rows_count = cursor.executemany(sql, (('[email protected]', 'test'   str(i)) for i in range(10000,20000)))
        connection.commit()
        print('executmany insert 10000 rows cost %s', time.time() - start)

    # executemany换成列表
    with connection.cursor() as cursor:
        # Create a new record
        start = time.time()
        sql = "INSERT INTO `users` (`email`, `password`) VALUES (%s, %s)"
        li = []
        for i in range(20000,30000):
            li.append(('[email protected]', 'test'   str(i)))
        rows_count = cursor.executemany(sql, li)
        connection.commit()
        print('executmany insert 10000 rows cost %s', time.time() - start)

finally:
   connection.close()

# execut insert 10000 rows cost %s 5.356306791305542
# executmany insert 10000 rows cost %s 0.09076881408691406
# executmany insert 10000 rows cost %s 0.08979249000549316

注:什么状态!!速度差了50多倍,executmany速度感人呀

 

6、fetchone、fetchmany、fetchall剖析

① fetchone 查询一条数据,为空时重返None

源码:

def fetchone(self):
    """Fetch the next row"""
    self._check_executed()       # 检查是否有sql语句执行过
    if self._rows is None or self.rownumber >= len(self._rows):
        return None      # _rows所有匹配项的集合,列表形式
    result = self._rows[self.rownumber]   # 返回第rownumber项数据
    self.rownumber  = 1
    return result

示例:

# fetchone方法
try:
    with connection.cursor() as cursor:   #查询
        # Read a single record
        sql = "SELECT `id`, `password` FROM `users` WHERE `email`=%s AND `password`=%s"
        cursor.execute(sql, ('[email protected]','test1'))
        result = cursor.fetchone()
        print(result)
        result = cursor.fetchone()
        print(result)
finally:
    connection.close()

# {'id': 31, 'password': 'test1'}
# {'id': 10031, 'password': 'test1'}

② fetchmany 查询钦点条数数据,为空时重回(卡塔 尔(阿拉伯语:قطر‎

源码:

def fetchmany(self, size=None):
    """Fetch several rows"""
    self._check_executed()
    if self._rows is None:
        return ()
    end = self.rownumber   (size or self.arraysize)
    result = self._rows[self.rownumber:end]
    self.rownumber = min(end, len(self._rows))
    return result

示例:

# fetchmany方法
try:
    with connection.cursor() as cursor:   #查询
        # Read a single record
        sql = "SELECT `id`, `password` FROM `users` WHERE `email`=%s AND `password`=%s"
        cursor.execute(sql, ('[email protected]','test1'))
        result = cursor.fetchmany(2)
        print(result)
        result = cursor.fetchmany(2)
        print(result)
finally:
    connection.close()

# [{'id': 31, 'password': 'test1'}, {'id': 10031, 'password': 'test1'}]
# [{'id': 20031, 'password': 'test1'}, {'id': 30031, 'password': 'test1'}]

③ fetchall 查询全部相配数据,为空时重临(卡塔尔

源码:

def fetchall(self):
    """Fetch all the rows"""
    self._check_executed()
    if self._rows is None:
        return ()
    if self.rownumber:
        result = self._rows[self.rownumber:]
    else:
        result = self._rows
    self.rownumber = len(self._rows)
    return result

示例:

# fetchall方法
try:
    with connection.cursor() as cursor:   #查询
        # Read a single record
        sql = "SELECT `id`, `password` FROM `users` WHERE `email`=%s AND `password`=%s"
        cursor.execute(sql, ('[email protected]','test10000'))
        result = cursor.fetchall()
        print(result)
        result = cursor.fetchall()
        print(result)
finally:
    connection.close()

# [{'id': 70030, 'password': 'test10000'}, {'id': 120030, 'password': 'test10000'}, {'id': 150030, 'password': 'test10000'}]
# []

解析:二种方法,都以先获得到推行sql语句的有着结果,最终实行切开获取,所以三种艺术的品质是相同的

 

计算:增、删、改、查的选用不再赘述,传入相应的sql语句就可以得到想要的结果

 

PyMySQL 1、安装 [[email protected] ~]# pip install PyMySQL 2、初识 成立数据表结构: mysql CREATE TABLE `users` (...

2.使用

改:

 

时下表里的数码是那么些:

分分快三计划 15

洗礼灵魂,修炼python(91卡塔尔-- 知识拾遗篇 —— pymysql模块之python操作mysql增加和删除改查分分快三计划。 

只怕地方那语句,只改了这一块,其余不改变 

分分快三计划 16

 

 运维结果:

分分快三计划 17

分分快三计划 18

 

完全OjbK

 

1.安装

使用pip安装:

 分分快三计划 19

洗礼灵魂,修炼python(91卡塔尔-- 知识拾遗篇 —— pymysql模块之python操作mysql增加和删除改查分分快三计划。 

pymysql

查:

#!usr/bin/env python
#-*- coding:utf-8 -*-
# author:yangva

import pymysql
# 创建数据库连接
conn = pymysql.connect(host='127.0.0.1',port=3306,user='root',passwd='',db='test',charset='utf8')

# 创建游标
cursor = conn.cursor()

# sql语句
cursor.execute('select * from user')
print(cursor.fetchone())

# 关闭连接
cursor.close()
conn.close()

小心:使用查询语句时毫不给commit()方法,因为从没退换数据,只是把数量拿出去而已,增加和删除改都有对数码实行改良的操作,所以要求采取commit

能领会啊?

查询时,须要动用fetchone(),fetchmany([int]),fetchall()来拿到查询的结果

 

fetchone运转结果:

分分快三计划 20

 

fetchmany运维结果:

分分快三计划 21

 

fetchall()运维结果:

分分快三计划 22

 

假若你想同不常间使用fetchone,fetchmany,fetchall的话,会成这么:

分分快三计划 23

 

自家想,你应有能看懂,不用笔者多说,反正你掌握它就和文件读取操作是如出少年老成辙的,都有指针

 

那么你说,笔者固然想同期利用方面包车型大巴八个fetch方法,何况笔者想让它都能正确的取到希望的多少,这些如何做吧?在文书操作里我们能够使用seek()来设定指针地方,那么这里运用什么措施吗?

 

cursor.scroll(0,mode='absolute') #相对当前位置移动
cursor.scroll(0,mode='relative') #相对绝对位置移动

 

没有错了,使用这几个scroll方法就足以改革指针地点了

 

好的,现在再看看:

分分快三计划 24

 

洗礼灵魂,修炼python(91卡塔尔-- 知识拾遗篇 —— pymysql模块之python操作mysql增加和删除改查分分快三计划。补充:要是您插入数据后,希望知晓自增列有稍许数量了,能够行使代码查看:

row = cursor.lastrowid

  

 

到家!O了,本篇博文截止

删:

 

先看下近来的数额:

 分分快三计划 25

 

平等的,其余不改变,就上边那意气风发段有生成

分分快三计划 26

 

运转结果:

分分快三计划 27

分分快三计划 28

 

一样的OjbK 

 

本文由分分快三计划发布,转载请注明来源

关键词: 分分快三计划