python 链接数据库mysql.connector模块

#/usr/bin/env/ python
# _*_ coding:utf-8 _*_
# author:linqinglong
from tests.conftest import connection

'''
show databases; 列出数据库所有的名
create database 数据库名 ;创建数据库
use 数据库;
show tables;列出所有表
create table 表名(字段1 varchar(20)，字段2 int(10),...)
insert into 表名 （字段，字段。。。） values(数值1，数值2...)
select *from 表名;
update 表名 set 字段 =‘数值’ ;
update 表名 set 字段 =‘数值’ where + ;
delete from 表名 ;
delete from 表名 where + ;

给表添加字段的命令如下：
　　alter table tableName add newColumn varchar(8) comment '新添加的字段'
　　注意：
　　1、在这句语句中需要注意的是，comment为注释，就像在java中//作用是一样的。
　　2、comment后需要加单引号将注释引起来。
alter table 表名 add 列名 类型;

eg:

MYSQL: 表加一字段并对现有记录赋值
　　加字段SQL：
　　alter table tasktemplate add TaskType varchar(60);
　　给新加字段赋值：
update tasktemplate set TaskType='AUTOTEST';
　　注释：给表tasktemplate 加一字段 TaskType 并且赋值为‘AUTOTEST';

执行删除命令，使用drop关键字。
基本的语法为：alter table <表名> drop column <字段名>;

update set命令用来修改表中的数据。
update set命令格式：update 表名 set 字段=新值,… where 条件;


参考：http://www.baike369.com/content/?id=5580
'''

'''
import mysql.connector

conn = mysql.connector.connect(host='127.0.0.1',user='root',password='password',database='test')
cursor = conn.cursor()

#ql = 'insert into user(name,grade) values(%s,%s),(qlong,2);'
#cursor.execute('select * from user where id = %s',('1',))
cursor.execute('select * from user')
values = cursor.fetchall()
print(values)

#cursor.execute('update user set grade='3' where id='5')

cursor.close()
conn.close()

'''

'''
import mysql.connector

conn = mysql.connector.connect(host='127.0.0.1',user='root',password='password',database='test')
cursor = conn.cursor()

#execute函数里面要用双引号。
cursor.execute("insert into user(id,name,grade,sex) values('4','MIke','2','boy')")
conn.commit()

cursor.execute("select * from user")
values = cursor.fetchall()
print(values)

cursor.close()
conn.close()

'''

'''

import mysql.connector
conn = mysql.connector.connect(host='127.0.0.1',user='root',password='password',database='test')
cursor = conn.cursor()

#execute函数执行sql语句
cursor.execute("select * from user where name='qlong'")
#fetchall函数得到execute函数的执行结果
values = cursor.fetchall()

cursor.close()
conn.close()

print(values)

'''

'''
#插入
import mysql.connector
conn = mysql.connector.connect(host='127.0.0.1',user='root',password='password',database='test')
cursor = conn.cursor()

#sql语句写为参数传入execute()函数中
sql = "insert into user(id,name,grade,sex) values(%s,%s,%s,%s)"
date =('6','Tracy','3','girl')

cursor.execute(sql,date)
conn.commit()

cursor.execute("select *from user")
values = cursor.fetchall()

cursor.close()
conn.close()

print(values)

'''

'''
#删除
import mysql.connector
conn = mysql.connector.connect(host='127.0.0.1',user='root',password='password',database='test')
cursor = conn.cursor()

#sql语句写为参数传入execute()函数中
sql = "delete from user where id=4"
cursor.execute(sql)
conn.commit()

cursor.execute("select *from user")
values = cursor.fetchall()

cursor.close()
conn.close()

print(values)
'''
'''
#更改
import mysql.connector
conn = mysql.connector.connect(host='127.0.0.1',user='root',password='password',database='test')
cursor = conn.cursor()

#sql语句写为参数传入execute()函数中
sql = "update user set name=%s where id=2"
#注意括号里面的逗号
date =('lqlong',)

cursor.execute(sql,date)
conn.commit()

cursor.execute("select *from user")
values = cursor.fetchall()

cursor.close()
conn.close()

print(values)
'''

'''
#插入多条数据
import mysql.connector
conn = mysql.connector.connect(host='127.0.0.1',user='root',password='password',database='test')
cursor = conn.cursor()

li = [
('7','alex','man'),
('8','sb','woman'),
]

#sql语句写为参数传入execute()函数中
sql = "insert into user(id,name,sex) values(%s,%s,%s)"

#executemany()函数必须传入两个参数，一个为sql语句，后面为对象；
cursor.executemany(sql,li)
conn.commit()

cursor.execute("select *from user")
values = cursor.fetchall()

cursor.close()
conn.close()

print(values)
'''

 

'''
#fetchone()函数

import mysql.connector
conn = mysql.connector.connect(host='127.0.0.1',user='root',password='password',database='test')
cursor = conn.cursor()

#execute函数执行sql语句
cursor.execute("select * from user ")
#fetchone函数得到execute函数的执行结果
print(cursor.fetchone())

print(cursor.fetchone())
print(cursor.fetchone())
print(cursor.fetchone())
print(cursor.fetchone())
print(cursor.fetchone())
print(cursor.fetchone())
print(cursor.fetchone())

cursor.close()
conn.close()
'''

'''
#fetchmany(num)函数

import mysql.connector
conn = mysql.connector.connect(host='127.0.0.1',user='root',password='password',database='test')
cursor = conn.cursor()

#execute函数执行sql语句
cursor.execute("select * from user ")
#fetchone函数得到execute函数的执行结果
print(cursor.fetchmany(5))

cursor.close()
conn.close()
'''

'''
#lastrowid用法,返回游标自增的值
import mysql.connector
conn = mysql.connector.connect(host='127.0.0.1',user='root',password='password',database='test')
cursor = conn.cursor()

#sql语句写为参数传入execute()函数中
sql = "insert into media (address) values(%s)"
#注意括号里面的逗号
date =('/usr/bin/a.txt',)

cursor.execute(sql,date)
conn.commit()

new_id = cursor.lastrowid
print(new_id)
sql = "insert into content(media_id) values(%s)"
date = (new_id,)
cursor.execute(sql,date)
conn.commit()

cursor.close()
conn.close()
'''

'''
三层架构：
数据访问层
业务处理层
展示层
'''

 

转载于:https://www.cnblogs.com/qlong/p/6407454.html