MySQL --- connector (how python operates database media, based on python), mysql --- python

Source: Internet
Author: User
Tags mysql import

MySQL --- connector (how python operates database media, based on python), mysql --- python
MySQL-connector Concept

They are usually a type of PythonPackageOr a class of Python libraries that have been written. These libraries provide basic functions for connecting to the database server using Python.

Since it is a package, we first learn to import this package

#! /usr/bin/env python3
# coding: utf-8
 
from mysql import connector
Print ('imported successfully ')

Run the above Code

$ python3 test_connector.py
Imported

Indicates that the related package or module has been imported successfully.

Connector syntax

To learn the connector syntax, see: https://dev.mysql.com/doc/connector-python/en/

Connection

The connection has two parts, which are indispensable:

  • Connection information:Host,Port,User Name,PasswordAndDatabase Name

    db_config = {
    'Host': 'localhost', # host
    'Port': 3306, # port
    'User ':'??? ', # User
    'Passwd ':'??? ', # Password
    'Db': 'python', # connected database
    }
  • Connection function: All connectors have functions namedconnect

     
    from mysql.connector import connect

    Note:: This function returns an object representing the connection.Connection objectAs a client.

Cursor

The cursor is obtained from a specific connection,cnnThere isCursor MethodTo return a cursor object.

cur = cnn.cursor()

Database operations are performed through cursors. Multiple cursors can be generated for a single connection.

Cursor, with oneExecute Method, You only need to executeSQL statementWhen passed as a parameter, you can makeConnectorCommandMySQL ServerExecute the statement.

Note::executeNo return value

So where is the galaxy?

In fact, the information is found in our cursor.

Because all the data we have found appears in the form of rows (each row is foundRecord), All exist in the cursor. When we iterate this cursor, we actually get the rows (Records) in sequence, and each time we output our iteration items, we output a row.

#! /usr/bin/env python3
# coding: utf-8
 
from mysql.connector import connect
 
db_config = {
    'host': 'localhost',
    'port': 3306,
    'user': '???',
    'passwd': '???',
'Db': 'mysql', # equivalent to automatic USE
}
 
cnn = connect(**db_config)
cur = cnn.cursor()
print(cur.execute('SELECT Host, User, authentication_string FROM user'))
 
for i in cur:
    print(i)

Execute the above Code

tuple@MyVM:~/testgit/learning_connector$ python3 test_cursor.py
None
(bytearray(b'localhost'), bytearray(b'root'), b'*81BCF9486900824FB0A3746219755FC21C50D058')
(bytearray(b'localhost'), bytearray(b'mysql.sys'), b'*THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE')
(bytearray(b'localhost'), bytearray(b'debian-sys-maint'), b'*599D0C45E62D3A9D58D74C0C5008688F13738AE5')
(bytearray(b'%'), bytearray(b'tuple'), b'*6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9')

The connector automatically helps us to automatically convert the data types in Python and Mysql.

Close

Use a connector. Just like using a file, remember to close it. The order is

 
#! /usr/bin/env python3
# coding: utf-8
 
from mysql.connector import connect
 
db_config = {
    'host': 'localhost',
    'port': 3306,
    'user': '???',
    'passwd': '???',
'Db': 'python', # equivalent to automatic USE
}
 
cnn = connect(**db_config)
cur = cnn.cursor()
 
sql_1 = '''
CREATE TABLE `from_connector` (
  `id` int PRIMARY KEY auto_increment,
  `name` varchar(20) NOT NULL
)
'''
 
sql_2 = '''
SHOW TABLES
'''
 
cur.execute(sql_1)
cur.execute(sql_2)
for i in cur:
    print(i)
    
cur.close()
Cnn. close ()

Ps: it has better things. I hope I can exchange and learn more with you;

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.