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

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;