Python uses MySQL database (new)

Source: Internet
Author: User

I've written an article before Python uses the MySQL Database blog, mainly using the Python2 and mysqldb drivers.

Python uses MySQL database

However,at the beginning of the year, I switched from Python2 to Python3,Python2 Already basically no longer used,the mysqldb drive stopped maintenance from the year 1 months. So, I'm going to write this blog again.

    • Python2---> Python3
    • MySQLdb-Pymysql

one , install pymysql

Python is a programming language,MySQL is a database, they are two different technologies , to enable Python to operate the MySQL database requires the use of drivers. the pymysql Drive is selected here . :

Https://pypi.python.org/pypi/PyMySQL

Https://github.com/PyMySQL/PyMySQL

Of course, the simplest way to install it is to use the pip command.

> Pip Install Pymysql

Replace the install with the show command to see if the Pymysql installation was successful.

Two, createMysqlTable

perform the following SQL statement, create a users table.

CREATE TABLE' users ' (' ID 'INT( One) not NULLauto_increment, ' email 'VARCHAR(255) COLLATE Utf8_bin not NULL, ' password 'VARCHAR(255) COLLATE Utf8_bin not NULL,    PRIMARY KEY(' id ')) ENGINE=INNODBDEFAULTCHARSET=UTF8 COLLATE=utf8_binauto_increment=1;

Three,PythonOperationMySQL

and then the point is, Python operates the MySQL database.

4.1 inserting data:

Importpymysql.cursors#connect to MySQL databaseConnection = Pymysql.connect (host='127.0.0.1', port=3306, user='Root', password='198876', db='Guest',
charset='UTF8MB4', cursorclass=pymysql.cursors.DictCursor)#create a cursor from the cursorcursor =connection.cursor ()#creates a SQL statement and executessql ="INSERT into ' users ' (' email ', ' password ') VALUES (' [email protected] ', ' 123456 ')"cursor.execute (SQL)#Submit SQLConnection.commit ()

regardless of the tool or library you are using, connecting to the database is essential. host IP addressof the database,port is the default port numberfor MySQL ,user for the user name of the data,password is the login password for the database, anddb is the name of the database.

The cursor () method creates a database cursor.

Execute () method executes the SQL statement.

Commit () actually commits the operation of the database to the data.

4.2. Querying Data

Importpymysql.cursors#connect to MySQL databaseConnection = Pymysql.connect (host='127.0.0.1', port=3306, user='Root', password='198876', db='Guest', charset='UTF8MB4', cursorclass=pymysql.cursors.DictCursor)#create a cursor from the cursorcursor =connection.cursor ()#perform a data querysql ="SELECT ' id ', ' password ' from ' users ' WHERE ' email ' = ' [email protected] '"cursor.execute (SQL)#querying database Single dataresult =Cursor.fetchone ()Print(Result)Print("-----------Ornate split line------------")#perform a data querysql ="SELECT ' id ', ' password ' from ' users '"cursor.execute (SQL)#querying a database for multiple dataresult =Cursor.fetchall () forDatainchResult:Print(data)#Turn off data connectionsConnection.close ()

the next operation is the database query.

Fetchone () is used to query a single piece of data.

Fetchall () is used to query more than one piece of data.

Close () don't forget to close the data connection at the end.

Operation Result:

{'Password':'123456','ID': 1}-----------Ornate Split Line------------{'Password':'123456','ID': 1}{'Password':'654321','ID': 2}

Python uses MySQL database (new)

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.