為了賬號安全,請及時綁定郵箱和手機立即綁定

Python--Python學習日記之MySQL增刪改查、批量插入等等

2017.07.27 11:53 12442瀏覽

最近閑置在家無聊玩起了Python,發現用Java平時寫的一些小程序如果使用Python的話代碼會更加精簡,Python使用起來和Java幾乎沒有區別,今天就先記錄下連接Mysql的一些簡單操作,獲取連接、插入、批量插入、修改、刪除等


首先連接Mysql

連接MySQL需要安裝插件,下載下來直接默認安裝就可以了

插件下載地址https://sourceforge.net/projects/mysql-python/

測試是否安裝成功,如下圖就是安裝成功了

# coding:UTF-8

import MySQLdb

print MySQLdb

圖片描述
連接數據庫代碼

# coding:UTF-8
import MySQLdb

conn = MySQLdb.Connect(
                        host = '127.0.0.1',
                        port = 3306,
                        user = 'root',
                        passwd = 'root',
                        db = 'springdemo', #數據庫名稱
                        charset = 'utf8'
                        )

cursor = conn.cursor()

print conn

print cursor

cursor.close()
conn.close()
查詢數據代碼
# coding:UTF-8
import MySQLdb

conn = MySQLdb.Connect(
                        host = '127.0.0.1',
                        port = 3306,
                        user = 'root',
                        passwd = 'root',
                        db = 'springdemo',
                        charset = 'utf8'
                        )

cursor = conn.cursor()

sql = "select * from spring_user"
cursor.execute(sql)

#輸出結果集有多少條數據
print cursor.rowcount

# 查詢頂部第一個數據
# rs = cursor.fetchone()
# print rs

# 查詢向下查詢n個數據
# rs = cursor.fetchmany(1)
# print rs

# 向下查詢全部數據
rs = cursor.fetchall()

for row in rs:
    #固定格式輸出
    print "id=%s,name=%s" % row
    #獲取結果第一個參數
    print row[0]

cursor.close()
conn.close()

執行結果
圖片描述

插入、修改、刪除操作
# coding:UTF-8
import MySQLdb

conn = MySQLdb.Connect(
                        host = '127.0.0.1',
                        port = 3306,
                        user = 'root',
                        passwd = 'root',
                        db = 'springdemo',
                        charset = 'utf8'
                        )

cursor = conn.cursor()

sql_insert = "insert into spring_user(id,nickname) values(10,'name10')"
sql_update = "update spring_user set nickname='update' where id=9"
sql_delete = "delete from spring_user where d<3"

sql = "select * from spring_user"

try:
    cursor.execute(sql_insert)
    print cursor.rowcount
    cursor.execute(sql_update)
    print cursor.rowcount
    cursor.execute(sql_delete)
    print cursor.rowcount

    conn.commit()

except Exception as e:
    print e
    conn.rollback()

cursor.close()
conn.close()
批量插入數據
# coding:UTF-8
import MySQLdb

conn = MySQLdb.Connect(
                        host = '127.0.0.1',
                        port = 3306,
                        user = 'root',
                        passwd = 'root',
                        db = 'springdemo',
                        charset = 'utf8'
                        )

cursor = conn.cursor()

sql_insert = "insert into spring_user(id,nickname) values(%s,%s)"

list=[]
data11=(11,"name11")
data12=(12,"name12")
data13=(13,"name13")

list.append(data11)
list.append(data12)
list.append(data13)

try:
    cursor.executemany(sql_insert,list)
    print cursor.rowcount
    conn.commit()

except Exception as e:
    print e
    conn.rollback()

cursor.close()
conn.close()

本人小小菜鳥,難免會有錯誤,希望大神們看到能夠及時指出。????? ????

點擊查看更多內容
2人點贊

若覺得本文不錯,就分享一下吧!

評論

相關文章推薦

正在加載中
意見反饋 幫助中心 APP下載
官方微信

舉報

0/150
提交
取消
lpl竞猜