python使用pymysql连接mysql_Python利用pymysql连接MySQL

大家现在都在利用pymysql这个包连接MySQL，然后对数据库进行操作，网上已经有很多描述如何进行增删改查的操作了，我这边就直接上代码，清晰明了；

本地连接mysql

connect = pymysql.connect(host='localhost',

port=3306,

user='u_name', #连接数据库名称

password='u_password', #连接数据库密码

db='u_db', #数据库名称

charset='utf8')

cursor_ins = connect.cursor() # 获取光标

通过ssh远程连接mysql

private_key = paramiko.RSAKey.from_private_key_file('/Users/xiaopi/.ssh/id_rsa') # mac私钥

with SSHTunnelForwarder(

# 指定ssh登录的跳转机的address

ssh_address_or_host=('192.168.10.5', 22),

# 设置密钥

ssh_pkey=private_key,

ssh_username='ssh_name',

# 设置数据库服务地址及端口

remote_bind_address=('192.168.40.12', 3306)) as server:

conn = pymysql.connect(database='u_db',

user='u_name',

password='u_password',

host='127.0.0.1', # 因为上面没有设置local_bind_address,所以这里必须是127.0.0.1,如果设置了，取设置的值就行了。

port=server.local_bind_port) # 这里端口也一样，上面的server可以设置，没设置取这个就行了

cursor_ins = conn.cursor() # 获取光标

增(两种方法)

# 批量插入

connect = pymysql.connect(host='localhost', port=3306, user='u_name', password='u_password', db='u_db', charset='utf8')

students = [(1,10,'男',4),(2,10,'女',4)] # 注意每个list的内容要用括号扩起来

insert_sql = "insert into student (id,age,sex,grade) values (%s,%s,%s,%s)"

cursor_ins = connect.cursor() # 获取光标

try:

# 注意这里使用的是executemany而不是execute，下边有对executemany的详细说明

cursor_ins.executemany(executesql, executevalues)

connect.commit()

print(u'更新成功...')

except Exception as e:

print(u'更新错误...', e)

connect.rollback()

finally:

cursor_ins.close()

connect.close()

# 单条插入

connect = pymysql.connect(host='localhost', port=3306, user='u_name', password='u_password', db='u_db', charset='utf8')

insert_sql = "insert into student (id,age,sex,grade) values (%s,%s,%s,%s)"

cursor_ins = connect.cursor() # 获取光标

try:

cursor_ins.execute(insert_sql,(3,10,'男',4))

connect.commit()

print(u'更新成功...')

except Exception as e:

print(u'更新错误...', e)

connect.rollback()

finally:

cursor_ins.close()

connect.close()

查、改、删

connect = pymysql.connect(host='localhost', port=3306, user='u_name', password='u_password', db='u_db', charset='utf8')

cursor = connect.cursor() # 获取光标

# 查询

select_sql = "SELECT * from student"

cursor.execute(select_sql)

print(u'查询成功...')

datas = cursor.fetchall() #获取查询结果

for data in datas:

print(data)

# 修改

update_sql = "UPDATE student SET grade = '5' WHERE `id` = '3'"

cursor.execute(update_sql)

connect.commit()

print(u'修改成功...')

# 删除

delete_sql = "DELETE from student WHERE `id` = '3'"

rows = cursor.execute(delete_sql)

print("待更新行数：" + str(rows))

connect.commit()

print(u'删除成功...')

cursor.close() #关闭光标

connect.close() #关闭连接

问题汇总

遇到一个插入时比较坑的问题在此记录一下；

在插入数据时，如果sql语句写成以下形式是无法插入str类型的数据的

"insert into `student` (`id`,`age`,`sex`,`grade`) values (`%s`,`%s`,`%s`,`%s`)"

此时插入str类型的数据会报以下错误：

(1064, "You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '男'')' at line 1")

主要问题在sql语句中已添加了引号，在sql解析的时候会提醒多插入引号的错误，此时只需要将sql中的 “`”去掉即可。