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中的 “`”去掉即可。