Use Python to operate Mysql Databases and pythonmysql Databases
#-*-Coding: utf8-*-_ author _ = 'angjing' import MySQLdb # query. Def select (host = "", user = "root", password = "", port = 3306, db = "", SQL = ""): connect = MySQLdb. connect (host = host, user = user, passwd = password, port = port, db = db, charset = 'utf8') cursor = connect. cursor () try: cursor.exe cute (SQL) result = cursor. fetchall () failed t Exception as ex: print ex cursor. close () connect. close () return result # insert. Multiple records can be inserted, for example, value = ("abc"), ("def"); when a single record is inserted, for example, value = ("abc "),) def insert (host = "", user = "root", password = "", port = 3306, db = "", SQL = "", value = ()): connect = MySQLdb. connect (host = host, user = user, passwd = password, port = port, db = db) cursor = connect. cursor () try: count = cursor.exe cute.pdf (SQL, value) connect. commit () commit t Exception as ex: print ex connect. rollback () cursor. close () connect. close () print u "Total number of affected records: % d" % Count # update and delete. Def updAndDel (host = "", user = "root", password = "", port = 3306, db = "", SQL = ""): connect = MySQLdb. connect (host = host, user = user, passwd = password, port = port, db = db) cursor = connect. cursor () try: count = cursor.exe cute (SQL) connect. commit () commit t Exception as ex: print ex connect. rollback () cursor. close () connect. close () print u "affects the total number of records: % d" % count # Multiple SQL statements are executed as one transaction. Def multiSql (host = "", user = "root", password = ", port = 3306, db =" ", sqllist = []): connect = MySQLdb. connect (host = host, user = user, passwd = password, port = port, db = db) cursor = connect. cursor () count = 0 try: for SQL in sqllist: count = count + cursor.exe cute (SQL) connect. commit () commit t Exception as ex: print ex connect. rollback () cursor. close () connect. close () print u "Total number of affected records: % d" % count # Run the Stored procedure def procedure (host = "", user = "root", password = "", port = 3306, db = "", procename = "", para = (): connect = MySQLdb. connect (host = host, user = user, passwd = password, port = port, db = db) cursor = connect. cursor () try: cursor. callproc (procename, para) connect. commit () commit t Exception as ex: print ex connect. rollback () cursor. close () connect. close () # query example # select (host = "172.16.3.38", password = "123456", db = "pytester", SQL = "select * from app limit 1 ") # insert example # insert (host = "172.16.3.38", password = "123456", db = "pytester", SQL = "insert into pybug_pybugs (title, content) values (% s, % s) ", value = (" abc "," efg "), (" qwe "," wwww "))) # update and delete example # updAndDel (host = "172.16.3.38", password = "123456", db = "pytester ", SQL = "update pybug_pybugs set title = 'xxxx1' where id = 33") # use multiple SQL statements as a transaction example # multiSql (host = "172.16.3.38", password = "123456 ", db = "pytester", sqllist = ["update pybug_pybugs set title = 'xxxx1' where id = 33", "update pybug_pybugs set title = 'xxxx1' where id = 31"]) # Stored procedure example # procedure (host = "172.16.3.38", password = "123456", db = "pytester", procename = "test1", para = ("wowo ", "haha "))
Copyright Disclaimer: This article is an original article by the blogger and cannot be reproduced without the permission of the blogger.