python操作mysql数据库

python标准数据库接口为python DB-API，python DB-API为开发人员提供了数据库应用编程接口

python DB-API使用流程：

• 引入API模块
• 获取与数据库的连接
• 执行SQL语句和存储过程
• 关闭数据库连接
  使用MySQLdb库
  import MySQLdb
  实例：

import MySQLdb
#打开数据库连接
db=MySQLdb.connect("localhost","testuser","test123","TESTDB",charset='utf8')
#使用cursor()方法获取操作游标
cursor=db.cursor()
#使用execute方法执行SQL语句
cusor.execute("SELECT VERSION()")
#使用fetchone()方法获取一条数据
data=cursor.fetchone()
print("database version :%s"%data)
#关闭数据库连接
db.close()

创建数据库表

cursor.execute("DROP TABLE IF EXISTS EMPLOYEE")
#创建数据表sql语句
sql="""CREATE TABLE EMPLOYEE(
       FIRST_NAME CHAR(20) NOT NULL.
       LAST_NAME CHAR(20),
       AGE INT,
       SEX CHAR(1),
       INCOME FLOAT)"""
cursor.execute(sql)

数据库插入操作

sql="""INSERT INTO EMPLOYEE(FIRST_NAME,
       LAST_NAME.AGE,SEX,INCOME)
       VALUES('Mac','Mohan',20,'M',2000)"""
try:
    #执行sql语句
    cursor.execute(sql)
    #提交到数据库执行
    db.commit()
except:
    db.rollback()

数据库查询操作

sql="SELECT * FROM EMPLOYEE \
     WHERE INCOME > %s" %(1000)
try:
    #执行sql语句
    cursor.execute(sql)
    #获取所有记录列表
    results=cursor.fetchall()
    for row in results:
       fname=row[0]
       lname=row[1]
       age=row[2]
       sex=row[3]
       income=row[4]
       #打印结果
       print "fname=%s,lname=%s,age=%s,sex=%s,income=%s" %\           (fname,lname,age,sex,income)
except:
     print("Error:unable to fecth data")

读取数据

improt pymsql
db=pymysql.connect(host='localhost',user='root',password='test',database='test',port=3306,charset='utf8')
user=pd.read_sql('select * from table',con=db)
db.colse()

SQL函数

聚合函数

AVG(),COUNT(),MAX(),MIN(),SUM(),复制聚合后，为了为聚合的值增加条件选择，使用having语句

转换函数

convert(data_type[(length)],expression[,style])

CONVERT(VARCHAR(10),日期字段名,120) --120 为日期格式YYYY-MM-DD

cast(expression as data_type)

cast与convert区别，convert更多用在时间格式转换，cast更多用在数值类型转换

日期函数

getdate():当前系统日期

dateadd(日期部分,number,date):返回增加数字(number)日期后的date值

datediff(日期部分,date1,date2):返回两个日期的差值

datename(日期部分,date):返回日期中部分字符形式

datepart(日期部分,date):返回日期中部分整数形式

year(date):返回指定日期年份

month(date):返回指定日期月份

day(date):返回指定日期天数

数字函数

abs(num_expr) 返回数值表达式的绝对值。

acos(float_expr) 返回角(以弧度表示),它的余弦值近似于指定的浮点表达式。

asin(float_expr) 返回角(以弧度表示),它的正弦值近似于指定的浮点表达式。

atan(float_expr) 返回角(以弧度表示),它的正切值近似于指定的浮点表达式。

atn2(float_expr1, float_expr2) 返回角(以弧度表示),它的正切值在两个近似的浮点表达式之间。

ceiling(num_expr) 返回大于或等于数值表达式的最小整数。

cos(float_expr) 返回以浮点表达式表示的近似于指定角度(以弧度表示)的余弦三角函数的值。

cot(float_expr) 返回以浮点表达式表示的近似于指定角度(以弧度表示)的余切三角函数的值。

degrees(num_expr)返回数值表达式表示的弧度值对应的度值。

exp(float_expr) 根据指定的近似浮点表达式,返回指数值。

floor(num_expr) 返回小于或等于数值表达式的最大整数

log(float_expr) 根据指定的近似浮点表达式,返回自然对数值。

log10(float_expr) 根据指定的近似浮点表达式,返回以为底的对数。

pi() 返回常量值.141592653589793

power(num_expr,y) 返回幂为y的数值表达式的值。

radians(num_expr) 返回数值表达式表示的度值对应的弧度值。

rand([seed]) 随机返回的到之间的近似浮点值,可以对seed指定为整数表达式(可选)。

round(num_expr,length) 对数值表达式截取指定的整数长度,返回四舍五入后的值。

sign(num_expr) 对正数执行+1操作,对负数和零执行-1操作。

sin(float_expr) 返回以浮点表达式表示的近似于指定角度(以弧度表示)的正弦三角函数的值。

square(float_expr) 返回浮点表达式的平均值。

sqrt(float_expr) 返回指定的近似浮点表达式的平方根。

tan(float_expr) 返回以浮点表达式表示的近似于指定角度(以弧度表示)的正切三角函数的值。

字符串函数

Expr1+expr2 返回两个表达式的组合形式的字符串。

ASCII(char_expr) 返回表达式最左边字符的ASCⅡ代码值。

CHAR(int_expr) 返回到之间的整数表达式的ASCⅡ字符值。如果输入的值不在有效范围内,则返回NULL。

CHARINDEX(‘pattern’,char_expr) 返回字符表达式中指定模式的起始位置。

DIFFERENCE(char_expr1,char_expr2) 根据比较两个字符表达式的相似度,返回到之间的值。表示匹配度最佳。

LEN(char_expr) 返回字符表达式的长度。

LOWER(char_expr) 将字符表达式全部转换为小写。

LTRIM(char_expr) 返回删除掉前面空格的字符表达式。

PATINDEX(’%pattern%’,expr) 返回表达式中模式第一次出现的起始位置。返回表示不存在模式形式。

REPLICATE(char_expr,int_expr) 返回重复指定次数的字符表达式产生的字符串。

REVERSE(char_expr) 反转字符表达式。

RIGHT(char_expr,int_expr) 返回从字符表达式最右端起根据指定的字符个数得到的字符。

RTRIM(char_expr) 返回删除掉其后空格的字符表达式。

SOUNDEX(char_expr) 评估两个字符串的相似度后得到的位代码。

SPACE(int_expr) 返回包含指定空格数的字符串。

STR(float_expr[,length[,decimal]]) 返回浮点表达式的字符串表示法。

STUFF(char_expr1,start,length,char_expr2) 使用字符表达式替换字符表达式的一部分字符,从指定的位置开始替换指定的长度。

SUBSTRING(char_expr,start,length) 返回从字符表达式的指定位置开始,截取指定长度得到的字符集。

UPPER(char_expr) 将字符表达式全部转换为大写。