python连接mysql

执行语句

import pymysql

# 连接数据库

# 参数1：mysql服务所在主机的IP

# 参数2：用户名

# 参数3：密码

# 参数4：要连接的数据库名

#db = pymysql.connect("localhost", "root", "xxx", "paper")

db = pymysql.connect( 

host='xxx',

    port=xxx,

    user='root',

    passwd='xxx',

    db='paper',

    # charset='utf8'

    )

# 创建一个cursor对象

cursor = db.cursor()

sql = "select * from papers"

#执行sql语句

cursor.execute(sql)

#获取返回的信息

data = cursor.fetchall()

print(data)

#断开

cursor.close()

db.close()

创建表

import pymysql

db = pymysql.connect("xxx", "root", "xxx", "xxx")

cursor = db.cursor()

# 检查表是否存在，如果存则删除

cursor.execute("drop table if exists bandcard")

#建表

sql = 'create table bandcard(id int auto_increment primary key, money int not null)'

cursor.execute(sql)

cursor.close()

db.close()

插入表

import pymysql

db = pymysql.connect( 

host='xxx',

    port=xxx,

    user='root',

    passwd='xxx',

    db='paper',

    # charset='utf8'

    )

cursor = db.cursor()

sql = 'insert into papers values(0,0,0,0,0,0,0,0,0,0)'

try:

    cursor.execute(sql)

    db.commit()

except:

    # 如果提交失败，回滚到上一次数据

    db.rollback()

cursor.close()

db.close()

更新表

import pymysql

db = pymysql.connect("xxx", "root", "xxx", "xxx")

cursor = db.cursor()

sql = 'update bandcard set money=1000 where id=1'

try:

    cursor.execute(sql)

    db.commit()

except:

    # 如果提交失败，回滚到上一次数据

    db.rollback()

cursor.close()

db.close()

删除表

import pymysql

db = pymysql.connect("xxx", "root", "xxxx", "xxx")

cursor = db.cursor()

sql = 'delete from bandcard where money = 200'

try:

    cursor.execute(sql)

    db.commit()

except:

    # 如果提交失败，回滚到上一次数据

    db.rollback()

cursor.close()

db.close()

查询

'''

fetchone()

功能：获取下一个查询结果集，结果集是一个对象

fetchall()

功能：接收全部的返回的行

rowcount:是一个只读属性，返回execute()方法影响的行数

'''

import pymysql

db = pymysql.connect("xxx", "root", "xxx", "xxx")

cursor = db.cursor()

sql = 'select * from bandcard where money>400'

try:

    cursor.execute(sql)

    reslist = cursor.fetchall()

    for row in reslist:

        print("%d--%d" % (row[0], row[1]))

except:

    # 如果提交失败，回滚到上一次数据

    db.rollback()

cursor.close()

db.close()