Python 3.x Connection Database (Pymysql mode)

==================pymysql===================

Since the MySQLdb module does not yet support python3.x, python3.x need to install the Pymysql module if you want to connect to MySQL.

The Pymysql module can be installed via PIP. But if you're using the Pycharm IDE, you can use Project Python to install third-party modules.

[File] >> [settings] >> [project:python] >> [Project Interpreter ] >> [install button ]

Because Python unifies the interface of the database connection, Pymysql and MySQLdb are similar in the way they are used:

Pymysql. Connect () parameter description Host (str):      MySQL server address port (int):      MySQL server port number user (str):      User name passwd (str):    password db (str):        Database name CharSet (str):   The method supported by the connection encoding connection object cursor ()        uses the connection to create and return a cursor commit () to        commit the current transaction rollback ()      Rolls back the current transaction close ()         closes the connection cursor object supported methods Execute (OP)     executes a database query command Fetchone ()      gets the next line of the result set Fetchmany (size) Gets the next few rows of the result set Fetchall ()      gets all the rows in the result sets rowcount ()      returns the number of data bars or affects the number of rows close ()         closes the cursor object

==================MySQL===================

First, before connecting to the database, create a trading table that is easy to test the Pymysql function:

--MySQL dump 10.13 distrib 5.7.9, for Win64 (x86_64)----host:localhost Database:python-- ----------------------------------------------------------table structure for table ' trade '--DROP TABLE IF EXISTS' trade ';CREATE TABLE' Trade ' (' ID ' )int(4) unsigned not NULLauto_increment, ' name 'varchar(6) not NULLCOMMENT'user's real name', ' account 'varchar( One) not NULLCOMMENT'Bank Savings Account', ' saving 'decimal(8,2) unsigned not NULL DEFAULT '0.00'COMMENT'Account Savings Amount', ' expend 'decimal(8,2) unsigned not NULL DEFAULT '0.00'COMMENT'Total account Expenditure', ' income 'decimal(8,2) unsigned not NULL DEFAULT '0.00'COMMENT'Total account Income',  PRIMARY KEY(' id '),UNIQUE KEY' Name_unique ' (' name ')) ENGINE=InnoDB auto_increment=2 DEFAULTCHARSET=UTF8;----dumping data for table ' trade '--LOCK TABLES ' trade ' WRITE;INSERT  into' Trade 'VALUES(1,'Steve Jobs','18012345678',0.00,0.00,0.00); UNLOCK TABLES;

==================Python===================

Use Python script to implement additions and deletions and transaction processing, the source code is as follows:

Importpymysql.cursors#connecting to a databaseConnect =Pymysql. Connect (Host='localhost', Port=3310, the user='Woider', passwd='3243', DB='python', CharSet='UTF8')#Get cursorcursor =connect.cursor ()#Inserting Datasql ="INSERT into trade (name, account, saving) VALUES ('%s ', '%s ',%.2f)"Data= ('Lei June','13512345678', 10000) cursor.execute (SQL%data) Connect.commit ()Print('successfully inserted', Cursor.rowcount,'Bar Data')#Modifying Datasql ="UPDATE trade SET saving =%.2f WHERE account = '%s '"Data= (8888,'13512345678') cursor.execute (SQL%data) Connect.commit ()Print('successfully modified', Cursor.rowcount,'Bar Data')#Querying Datasql ="SELECT name,saving from trade WHERE account = '%s '"Data= ('13512345678',) cursor.execute (SQL%data) forRowinchCursor.fetchall ():Print("name:%s\tsaving:%.2f"%row)Print('co-check to find out', Cursor.rowcount,'Bar Data')#Delete Datasql ="DELETE from trade WHERE account = '%s ' LIMIT%d"Data= ('13512345678', 1) cursor.execute (SQL%data) Connect.commit ()Print('successfully deleted', Cursor.rowcount,'Bar Data')#Transaction ProcessingSql_1 ="UPDATE trade SET saving = saving + $ WHERE account = ' 18012345678 '"sql_2="UPDATE trade SET expend = expend + $ WHERE account = ' 18012345678 '"Sql_3="UPDATE Trade SET income = income + a WHERE account = ' 18012345678 '"Try: Cursor.execute (sql_1)#Increase in savingsCursor.execute (sql_2)#increased spendingCursor.execute (Sql_3)#Increase in revenueexceptException as E:connect.rollback ()#Transaction Rollback    Print('Transaction Failure', E)Else: Connect.commit ()#Transaction Commit    Print('Transaction Success', Cursor.rowcount)#Close Connectioncursor.close () connect.close ( )

================== test Results ===================

================== some feelings ===================

I have been exposed to the scripting language is mainly three, JS, PHP, Python.

I feel the most intelligent is PHP, the most convenient is JS, the most flexible is python.

Usually I use JS to do interactive effects, using PHP to build back-office services, using Python to do data processing.

Many people use Python to taunt PHP, saying that python elegant php is ugly.

I don't think indentation is elegant, because Python is designed for the terminal, so the code is more concise than other programming languages, but that doesn't mean that Python is efficient. and C's syntax can withstand the test of time, so the PHP hierarchy relative to Python will be clearer, when the larger the amount of code to realize this.

Often in the technical forum to see the "PHP is the best language in the World" review, I can only say that your speech only comes from your ignorance.

If you really think PHP is the best language, then I suggest you get in touch with other languages and avoid being a frog.

If you think this is a different irony, then I suggest you study PHP carefully, and you'll find out how funny your sarcasm is.

Originally JS, PHP, Python is the scripting language for different fields, they are the most outstanding language in these fields, only they cooperate with each other to better serve the project.

Rather than arguing about the quality of the language, it is better to feel the convenience it brings.

Python 3.x Connection Database (Pymysql mode)