MySQLdb创建mysql数据库表

其实mysqldb创建表是有限制的,要求是5.1-5.5版本

pymsql是Python中操作MySQL的模块,其使用方法和MySQLdb几乎相同。

1.下载

首先要下载下载:请到官方网站http://sourceforge.net/projects/mysql-python/或者点击链接下载http://downloads.sourceforge.net/project/mysql-python/mysql-python-test/1.2.3c1/MySQL-python-1.2.3c1.tar.gz?use_mirror=nchc

2.解压安装

解压:tar zxvf MySQL-python*

进入文件目录,运行以下命令:
python setup.py install

3. 用法

'''
# 打开数据库连接
db = MySQLdb.connect("192.168.72.131","root","","test" )
# 使用cursor()方法获取操作游标
cursor = db.cursor()
# 如果数据表已经存在使用 execute() 方法删除表。
cursor.execute("DROP TABLE IF EXISTS EMPLOYEE")
# 创建数据表SQL语句
sql = \"\"\"CREATE TABLE EMPLOYEE (
FIRST_NAME CHAR() NOT NULL,
LAST_NAME CHAR(),
AGE INT,
SEX CHAR(),
INCOME FLOAT )\"\"\"
cursor.execute(sql)
# 关闭数据库连接
db.close()
'''

pymsql

一、下载安装:

pip3 install pymysql

二、使用

1、执行SQL

#!/usr/bin/env python
# -*- coding:utf- -*-
import pymysql # 创建连接
conn = pymysql.connect(host='127.0.0.1', port=, user='root', passwd='', db='t1')
# 创建游标
cursor = conn.cursor() # 执行SQL,并返回收影响行数
effect_row = cursor.execute("update hosts set host = '1.1.1.2'") # 执行SQL,并返回受影响行数
#effect_row = cursor.execute("update hosts set host = '1.1.1.2' where nid > %s", (,)) # 执行SQL,并返回受影响行数
#effect_row = cursor.executemany("insert into hosts(host,color_id)values(%s,%s)", [("1.1.1.11",),("1.1.1.11",)]) # 提交,不然无法保存新建或者修改的数据
conn.commit() # 关闭游标
cursor.close()
# 关闭连接
conn.close()

2、获取新创建数据自增ID

#!/usr/bin/env python
# -*- coding:utf- -*-
import pymysql conn = pymysql.connect(host='127.0.0.1', port=, user='root', passwd='', db='t1')
cursor = conn.cursor()
cursor.executemany("insert into hosts(host,color_id)values(%s,%s)", [("1.1.1.11",),("1.1.1.11",)])
conn.commit()
cursor.close()
conn.close() # 获取最新自增ID
new_id = cursor.lastrowid

3、获取查询数据

cursor = conn.cursor(cursor=pymysql.cursors.DictCursor)---》 这么写可以让返回的值为字典
#!/usr/bin/env python
# -*- coding:utf- -*-
import pymysql conn = pymysql.connect(host='127.0.0.1', port=, user='root', passwd='', db='t1')
cursor = conn.cursor()
cursor.execute("select * from hosts") # 获取第一行数据
row_1 = cursor.fetchone() # 获取前n行数据
# row_2 = cursor.fetchmany()
# 获取所有数据
# row_3 = cursor.fetchall() conn.commit()
cursor.close()
conn.close()

注:在fetch数据时按照顺序进行,可以使用cursor.scroll(num,mode)来移动游标位置,如:

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

4、fetch数据类型

  关于默认获取的数据是元祖类型,如果想要或者字典类型的数据,即:

#!/usr/bin/env python
# -*- coding:utf- -*-
import pymysql conn = pymysql.connect(host='127.0.0.1', port=, user='root', passwd='', db='t1') # 游标设置为字典类型
cursor = conn.cursor(cursor=pymysql.cursors.DictCursor)
r = cursor.execute("call p1()") result = cursor.fetchone() conn.commit()
cursor.close()
conn.close()

5、插入数据注意的地方

    li = tuple(['hrYjT71474436254', '2016-09-21 13:37:34']) #这句是重点
print li
sql = "insert INTO `222` VALUES {}".format(li)
print sql
cursor.execute(sql)
conn.commit()

自己造轮子, 二次封装pymysql

class base_pymysql(object):
def __init__(self, host, port, user, password, db_name=None):
self.db_host = host
self.db_port = int(port)
self.user = user
self.password = str(password)
self.db = db_name
self.conn = None
self.cursor = None def connect(self):
self.conn = pymysql.connect(host=self.db_host, port=self.db_port, user=self.user,
passwd=self.password, db=self.db, charset="utf8")
self.cursor = self.conn.cursor(cursor=pymysql.cursors.DictCursor) class MyPymysql(base_pymysql):
"""
Basic Usage: ret = My_Pymysql('test1')
res = ret.selectone_sql("select * from aaa")
print(res)
ret.close()
--------------
class writer_information_tables():
def __init__(self, libname="metadata"):
self.libname = libname
self.res = MyPymysql('metadata') def insert_sql(self, data):
sql = '''insert INTO `meta_variable` SET DataTableID={}, VarValues="%s";'''.format(
data["DataTableID"])
value = (data["VarValues"])
# print(sql)
# self.res.idu_sql(sql)
self.res.insert_sql(sql, value=value)
def commit(self):
self.res.commit() def close(self):
self.res.close()
----------------
def CreateDataTableInfor(data):
sql = "insert into `meta_data_table` SET DataTableID='{}';".format(
data["DataTableID"])
ret = MyPymysql('metadata')
ret.idu_sql(sql)
ret.close()
--------------- Precautions:
Config.__init__(self, config_filename="my.cnf") """ def __init__(self, conf_name):
self.conf = Config().get_content(conf_name)
super(MyPymysql, self).__init__(**self.conf)
self.connect() def idu_sql(self, sql, value=None):
# adu: insert, delete, update的简写
# 考虑到多语句循环, try就不写在这里了
self.cursor.execute(sql, value)
self.conn.commit() def commit(self):
self.conn.commit() def rollback(self):
self.conn.rollback() def insert_sql(self, sql, value=None):
# adu: insert, delete, update的简写
# 防止sql注入
self.cursor.execute(sql, value)
def update_sql(self, sql, value=None):
# adu: insert, delete, update的简写
# 防止sql注入
self.cursor.execute(sql, value)
def delete_sql(self, sql, value=None):
# adu: insert, delete, update的简写
# 防止sql注入
self.cursor.execute(sql, value) def selectone_sql(self, sql):
self.cursor.execute(sql)
self.conn.commit()
return self.cursor.fetchone() def selectall_sql(self, sql):
self.cursor.execute(sql)
self.conn.commit()
return self.cursor.fetchall() def select_sql(self, sql, value=None):
# 防止sql注入
self.cursor.execute(sql, value)
self.conn.commit()
return self.cursor.fetchall() def close(self):
self.conn.close()
self.conn = None
self.cursor = None

自己造的轮子代码

class Config(object):
"""
# Config().get_content("user_information")
""" def __init__(self, config_filename="zk_css.cnf"):
file_path = os.path.join(os.path.dirname(os.path.dirname(__file__)), config_filename)
self.cf = configparser.ConfigParser()
self.cf.read(file_path) def get_sections(self):
return self.cf.sections() def get_options(self, section):
return self.cf.options(section) def get_content(self, section):
result = {}
for option in self.get_options(section):
value = self.cf.get(section, option)
result[option] = int(value) if value.isdigit() else value
return result

Config类

class base_pymysql(object):
def __init__(self, host, port, user, password, db_name=None, cursor=pymysql.cursors.DictCursor):
self.db_host = host
self.db_port = int(port)
self.user = user
self.password = str(password)
self.db = db_name
self.conn = None
self.cursor = cursor def connect(self):
self.conn = pymysql.connect(host=self.db_host, port=self.db_port, user=self.user,
passwd=self.password, db=self.db, charset="utf8")
self.cursor = self.conn.cursor(cursor=self.cursor) class MyPymysql(base_pymysql):
"""
Basic Usage: ret = My_Pymysql('test1')
res = ret.selectone_sql("select * from aaa")
print(res)
ret.close()
--------------
class writer_information_tables():
def __init__(self, libname="metadata"):
self.libname = libname
self.res = MyPymysql('metadata') def insert_sql(self, data):
sql = '''insert INTO `meta_variable` SET DataTableID={}, VarValues="%s";'''.format(
data["DataTableID"])
value = (data["VarValues"])
# print(sql)
# self.res.idu_sql(sql)
self.res.insert_sql(sql, value=value)
def commit(self):
self.res.commit() def close(self):
self.res.close()
----------------
def CreateDataTableInfor(data):
sql = "insert into `meta_data_table` SET DataTableID='{}';".format(
data["DataTableID"])
ret = MyPymysql('metadata') 或者 MyPymysql('metadata', None), 返回结果将是两种不同的形态 {} 和 ()
ret.idu_sql(sql)
ret.close()
--------------- Precautions:
Config.__init__(self, config_filename="my.cnf") """ def __init__(self, conf_name, cursor=pymysql.cursors.DictCursor):
self.conf = Config().get_content(conf_name)
super(MyPymysql, self).__init__(**self.conf, cursor=cursor)
self.connect() def idu_sql(self, sql, value=None):
# adu: insert, delete, update的简写
# 考虑到多语句循环, try就不写在这里了
self.cursor.execute(sql, value)
self.conn.commit() def commit(self):
self.conn.commit() def rollback(self):
self.conn.rollback() def insert_sql(self, sql, value=None):
# adu: insert, delete, update的简写
# 防止sql注入
self.cursor.execute(sql, value)
def update_sql(self, sql, value=None):
# adu: insert, delete, update的简写
# 防止sql注入
self.cursor.execute(sql, value)
def delete_sql(self, sql, value=None):
# adu: insert, delete, update的简写
# 防止sql注入
self.cursor.execute(sql, value) def selectone_sql(self, sql):
self.cursor.execute(sql)
self.conn.commit()
return self.cursor.fetchone() def selectall_sql(self, sql):
self.cursor.execute(sql)
self.conn.commit()
return self.cursor.fetchall() def select_sql(self, sql, value=None):
# 防止sql注入
self.cursor.execute(sql, value)
self.conn.commit()
return self.cursor.fetchall() def close(self):
self.conn.close()
self.conn = None
self.cursor = None

升级版, 改变返回值形态

注意:少写了一句: def close(self):里面需要增加一句: self.cursor.close() ------>忘了

class A():

    def bb(self):
sql = \
"""
select * from db_metadata.meta_project limit ;
"""
data = self.pymysql.selectall_sql(sql)
return data class DSF(A):
def __init__(self, libname="notdbMysql"):
self.libname = libname
self.pymysql = MyPymysql(self.libname) def aa(self):
sql = \
"""
select * from db_metadata.meta_project limit ; """
data = self.pymysql.selectall_sql(sql)
return data def close(self):
self.pymysql.close() if __name__ == '__main__':
ret = DSF()
print(ret.aa())
print(ret.bb())
ret.close()

model 类使用方法

class CreateProjectProduce(SelectInfo):
def __init__(self, libname="notdbMysql"):
self.libname = libname
self.pymysql = MyPymysql(self.libname) def __enter__(self):
return self def check_the_data(self, ProjectID):
'''
explain:
检查核对数据是否存在
:return:
'''
     # 调用继承类方法
data = self.CatSimpleProjectProduceModel(ProjectID) return data def insert_sql(self, data):
sql = '''insert INTO `meta_variable` SET DataTableID={}, VarValues="%s";'''.format(
data["DataTableID"])
value = (data["VarValues"])
self.pymysql.insert_sql(sql, value=value) def close(self):
self.pymysql.close() def __exit__(self, exc_type, exc_val, exc_tb):
self.close()

Use Method

#!/usr/bin/env python
# -*- coding:utf-8 -*- import os, configparser, pymysql '''
1. Config 类的 my.cnf 需要指定路径
2. my.cnf 的文件详情
my.cnf: # -----<notdbMysql>----- #配置中没有写库的名字,这样更加灵活
[notdbMysql]
host = 192.168.2.137
port = 3306
user = root
password = python123
3. 返回值形态设置
class writer_information_tables():
def __init__(self, libname="notdbMysql"):
self.libname = libname
self.res = MyPymysql(self.libname) # --> 返回值形态是{'aid': 5, 'varlabel': 'adadsf', 'vid': 0}
# self.res = MyPymysql(self.libname, None) # --> 返回值形态是(0, 5, 'adadsf')
''' class Config(object):
"""
# Config().get_content("user_information")
""" def __init__(self, config_filename="my.cnf"):
file_path = "/opt/code/my_code/tornado_uedit/my.cnf"
self.cf = configparser.ConfigParser()
self.cf.read(file_path) def get_sections(self):
return self.cf.sections() def get_options(self, section):
return self.cf.options(section) def get_content(self, section):
result = {}
for option in self.get_options(section):
value = self.cf.get(section, option)
result[option] = int(value) if value.isdigit() else value
return result class base_pymysql(object):
def __init__(self, host, port, user, password, db_name=None, cursor=pymysql.cursors.DictCursor):
self.db_host = host
self.db_port = int(port)
self.user = user
self.password = str(password)
self.db = db_name
self.conn = None
self.cursor = cursor def connect(self):
self.conn = pymysql.connect(host=self.db_host, port=self.db_port, user=self.user,
passwd=self.password, db=self.db, charset="utf8")
self.cursor = self.conn.cursor(cursor=self.cursor) class MyPymysql(base_pymysql):
"""
Basic Usage: ret = My_Pymysql('test1')
res = ret.selectone_sql("select * from aaa")
print(res)
ret.close()
--------------
class writer_information_tables():
def __init__(self, libname="metadata"):
self.libname = libname
self.res = MyPymysql('metadata') def insert_sql(self, data):
sql = '''insert INTO `meta_variable` SET DataTableID={}, VarValues="%s";'''.format(
data["DataTableID"])
value = (data["VarValues"])
# print(sql)
# self.res.idu_sql(sql)
self.res.insert_sql(sql, value=value)
def commit(self):
self.res.commit() def close(self):
self.res.close()
----------------
def CreateDataTableInfor(data):
sql = "insert into `meta_data_table` SET DataTableID='{}';".format(
data["DataTableID"])
ret = MyPymysql('metadata') 或者 MyPymysql('metadata', None), 返回结果将是两种不同的形态 {} 和 ()
ret.idu_sql(sql)
ret.close()
--------------- Precautions:
Config.__init__(self, config_filename="my.cnf") """ def __init__(self, conf_name, cursor=pymysql.cursors.DictCursor):
self.conf = Config().get_content(conf_name)
super(MyPymysql, self).__init__(**self.conf, cursor=cursor)
self.connect() def idu_sql(self, sql, value=None):
# adu: insert, delete, update的简写
# 考虑到多语句循环, try就不写在这里了
self.cursor.execute(sql, value)
self.conn.commit() def commit(self):
self.conn.commit() def rollback(self):
self.conn.rollback() def insert_sql(self, sql, value=None):
# adu: insert, delete, update的简写
# 防止sql注入
self.cursor.execute(sql, value) def update_sql(self, sql, value=None):
# adu: insert, delete, update的简写
# 防止sql注入
self.cursor.execute(sql, value) def delete_sql(self, sql, value=None):
# adu: insert, delete, update的简写
# 防止sql注入
self.cursor.execute(sql, value) def selectone_sql(self, sql):
self.cursor.execute(sql)
self.conn.commit()
return self.cursor.fetchone() def selectall_sql(self, sql):
self.cursor.execute(sql)
self.conn.commit()
return self.cursor.fetchall() def select_sql(self, sql, value=None):
# 防止sql注入
self.cursor.execute(sql, value)
self.conn.commit()
return self.cursor.fetchall() def close(self):
self.conn.close()
self.conn = None
self.cursor = None class writer_information_tables():
def __init__(self, libname="notdbMysql"):
self.libname = libname
self.res = MyPymysql(self.libname) # --> 返回值形态是{'aid': 5, 'varlabel': 'adadsf', 'vid': 0}
# self.res = MyPymysql(self.libname, None) # --> 返回值形态是(0, 5, 'adadsf') def insert_sql(self):
sql = "select * from myTest.a1"
data = self.res.selectone_sql(sql)
return data def commit(self):
self.res.commit() def close(self):
self.res.close() if __name__ == '__main__':
pym_obj = writer_information_tables()
print(pym_obj.insert_sql())
pym_obj.close()

Use Method

Pymysql 进行事务回滚

#!/usr/bin/env python
import MySQLdb def connect_mysql():
db_config = {
'host': '127.0.0.1',
'port': ,
'user': 'root',
'passwd': 'pzk123'
}
c = MySQLdb.connect(**db_config)
return c if __name__ == '__main__':
c = connect_mysql() # 首先连接数据库
cus = c.cursor() # 生成游标对象
sql = 'drop database test;' # 定义要执行的SQL语句
try:
cus.execute(sql) # 执行SQL语句
c.commit() # 如果执行成功就提交事务
except Exception as e:
c.rollback() # 如果执行失败就回滚事务
raise e
finally:
c.close() # 最后记得关闭数据库连接

mysqldb的

PYMYSQL防止sql注入问题

1.寻找到SQL注入的位置

2.判断服务器类型和后台数据库类型

3.针对不通的服务器和数据库特点进行SQL注入攻击

案例:

1、字符串拼接查询,造成注入

import pymysql

conn = pymysql.connect(host='127.0.0.1', port=3306, user='root', passwd='', db='User')
cursor = conn.cursor()
username=input()
password =input()
# 正常构造语句的情况
sql = "select user,pwd from User where user='%s' and pwd='%s'" % (username,password) row_count = cursor.execute(sql)
row_1 = cursor.fetchone()
print(row_count, row_1)
conn.commit()
cursor.close()
conn.close()

案例

其实用户可以这样输入实现免帐号登录:

username: ‘or 1 = 1 –-

password:

如若没有做特殊处理,那么这个非法用户直接登陆进去了.

当输入了上面的用户名和密码,服务端的sql就变成:

sql = "select user,pwd from User where user=‘'or 1 = 1 –-' and pwd='%s'"

因为条件后面username=”or 1=1 用户名等于 ” 或1=1 那么这个条件一定会成功;然后后面加两个-,这意味着注释,它将后面的语句注释,让他们不起作用,这样语句永远都能正确执行,用户轻易骗过系统,获取合法身份。

解决方法:

注意:一定要用"%s" 而不是‘%s’

#! /usr/bin/env python
# -*- coding:utf-8 -*-
import pymysql conn = pymysql.connect(host='127.0.0.1', port=3306, user='root', passwd='', db='User')
cursor = conn.cursor(cursor=pymysql.cursors.DictCursor)
username= input()
password =input() #执行参数化查询
row_count=cursor.execute("select user,pwd from User where user='%s' and pwd='%s'" ,(username,password))
#execute()函数本身就有接受SQL语句变量的参数位,只要正确的使用(直白一点就是:使用”逗号”,而不是”百分号”)就可以对传入的值进行correctly转义,从而避免SQL注入的发生。 #内部执行参数化生成的SQL语句,对特殊字符进行了加\转义,避免注入语句生成。
# sql=cursor.mogrify("select user,pwd from User where user='%s' and pwd='%s'" ,(username,password))
# print (sql) row_1 = cursor.fetchone()
print(row_count,row_1) conn.commit()
cursor.close()
conn.close()

防sql注入问题2

报错:TypeError: %d format: a number is required, not str"

cursor.execute("""
insert into tree (id,parent_id,level,description,code,start,end)
values (%d,%d,%d,%s,%s,%f,%f)
""", (1,1,1,'abc','def',1,1)
)

如果这么写报错

对应mysql表类型

id int(),
parent_id int(),
level int(),
description varchar(),
code varchar(),
start decimal(,),
end decimal(,)
" File "/usr/lib/pymodules/python2./MySQLdb/cursors.py", line 151, in execute query = query % db.literal(args)

TypeError: %d format: a number is required, not str"

解决办法:

The format string is not really a normal Python format string. You must always use %s for all fields.

防sql注入问题3

pymysql防sql注入必须是"%s", 可以这种%s形式的
from collections import OrderedDict
from common.Base import MyPymysql d = {'is_top': '', 'input_data_text': 'adfadf', 'is_comment': '', 'title': 'adsf', 'tag': ';分离', 'input_data_html': , 'is_release': '', 'input_data_content': '<p>adfadf<br/></p>', 'is_homepage': '', 'Password': '', 'edit_category': '', 'is_password': ''} def AddArticleModel(info):
sql = """
INSERT INTO blog.text
SET
ArticleTitle="%s", Release=%s ----> 这样是错的, pymysql防sql注入必须是"%s", 不能用这种%s形式的,包括数据是int都不行《---可以%s,留着这句话,看看当时因为release关键字让自己写的傻逼笔记 """
ret = MyPymysql('notdbMysql')
value = (
info["title"], # ArticleTitle int(info["is_release"]), # Release )
ret.idu_sql(sql, value=value)
ret.close() if __name__ == '__main__':
AddArticleModel(d)

三、错误集锦

1.

查看mysql单个库大小容量的命令(单位为字节,得到多少M需要除以1048576)

SELECT sum(DATA_LENGTH)+sum(INDEX_LENGTH) FROM information_schema.TABLES where TABLE_SCHEMA='数据库名';

2.

变量名为关键字(尤其前朝余孽留下的坑,特别注意了需要加``)

update `test` set a='' where `index`=;  这个index耗费了我2个小时,下午5点头晕沉沉的

3.

那么转换一下,上次是测试的语句,下面就直接换正式的了, %s也加引号,视情况而定

sql2 = "update `%s` set a10b1j='%s' where `index`='%s'" % (table_name, li[j], j)

4. 

2.7存入mysql以后unicode的转义解决办法

"""
import pymysql
conn = pymysql.connect(host='112.126.70.69', port=, user='datapl', passwd='Rome78Uj', db='data_index_test')
cursor = conn.cursor(cursor=pymysql.cursors.DictCursor)
cursor.execute("select cid FROM data_bak")
row_3 = cursor.fetchall()
li = [i[] for i in row_3]
err_li = []
for i in range(len(li)):
try:
print i
a, json_sumdata = VSummary().do_(, li[i])
sql = "INSERT INTO data_cid_json SET cid='%s', json_sumdata='%s'" % (li[i], pymysql.escape_string(json_sumdata))
cursor.execute(sql)
conn.commit()
except Exception as e:
err_li.append(li[i])
continue
cursor.close() conn.close() """

5.

调用oracle的时候需要注意的问题

他说一直是编码的问题,其实是oracle的问题

6.

解决办法

在创建连接的时候设置一下编码,如:

conn = MySQLdb.connect(host="localhost", user="root", passwd="root", db="db",  charset="utf8")

7. 注意一定要用"%s", 而不能用'%s'

8. 在pymysql里面防sql要注意, 例如: update `%s`  这个时候会把表名也转义, 所以报错

会变成update `\'meta_project\' set ....这样就找不到表名

之前这么写的

最新文章

  1. BestCoder#51
  2. Oracle Linux 5.7安装VMware Tools的问题
  3. Linux下安装mongodb详细过程
  4. C# Get/Post 模拟提交
  5. IntelliJ IDEA 2016.2.4下载与注册码
  6. HDU2859 Phalanx 简单DP
  7. Chromium如何显示Web页面
  8. perl的一些基本用法
  9. win7 安装 sql2000
  10. Canada Cup 2016 C. Hidden Word
  11. 关于websorm卡顿的问题
  12. log.go 源码阅读
  13. FTP软件FileZilla 3.38.1下载
  14. 对象引用对于非静态的字段、方法、属性Program.Print()是必需的
  15. Java多线程(一)多线程基础
  16. 运维与自动化系列③自动化部署基础与shell脚本实现
  17. C#Winform的DEV下拉下拉控件介绍
  18. 10种JavaScript开发者必备的VS Code插件
  19. windows go dll 框架
  20. JS中深浅拷贝 函数封装代码

热门文章

  1. 简单修改文件名python脚本
  2. hdu 2544 最短路(两点间最短路径)
  3. JVM Troubleshooting
  4. Azure产品目录
  5. Standard C 之 math.h和float.h
  6. Spark SQL编程指南(Python)【转】
  7. Android NDK之二:创建NativeActivity
  8. 【Linux】svn添加用户
  9. C# Dictionary通过value获取对应的key值
  10. pual_bot 天气插件编写