Shulou(Shulou.com)06/02 Report--

This article mainly shows you "how to achieve database operation in Python", the content is easy to understand, clear, hope to help you solve your doubts, the following let the editor lead you to study and learn "how to achieve database operation in Python" this article.

1. Mysql database:

Install pymysql:

Pip install pymysql

1. Common methods of database connection object connect:

Cursor () # create a cursor object

Commit () # commit transaction

Rollback () # transaction rollback

Close () # close the database connection

2. Common methods of cursor object cursor:

Execute () # execute SQL statement

Executemany () # is used to execute multi-day SQL statements

Close () # used to close cursors

Fetchone () # is used to fetch a record from the result and point the cursor to the next record

Fetchall () # fetch all records from the result

Scroll () # for cursor scrolling

From myconn import myconn

Class Sql:

Def _ init__ (self,db=None):

# initialize the connection to the database

# self.conn = pymysql.connect (db=dataName, user= "user", passwd= "passwd", host= "host", charset='utf8')

# this is the method I wrote myself. Normally, install pymysql and connect with the above sentence

Self.conn = myconn.get_conn (db) if DBMS none else myconn.get_conn ()

Def get_databases (self):

'' get all database names''

Return self.getSqlData ("show databases")

Def get_tables (self):

'' get all table names''

Return self.getSqlData ("show tables")

Def create_table (self,table):

'' create a table''

Cur = self.conn.cursor ()

# id: unsigned integer, primary key, self-increment; name: string; age: unsigned integer

Sql = "" create table {} (id int unsigned primary key auto_increment name varchar (10), age int unsigned) "" .format (table)

Cur.execute (sql)

Self.conn.commit ()

Def

Insert_data (self,table):

'' add a piece of data to the data table''

Cur = self.conn.cursor ()

Sql = "insert into {} (name,age) values (% s)"

Cur.execute (sql, ('myName',80))

Self.conn.commit ()

Def update_data (self,table):

'modify the data in the table'

Cur = self.conn.cursor ()

Sql = "update {} set age=18 where name='myName'" .format (table)

Cur.execute (sql)

Self.conn.commit ()

Def select_data (self, table):

'' query data from database tables''

Cur = self.conn.cursor ()

Sql = "select name,age from {}" .format (table)

Cur.execute (sql)

Return cur.fetchall ()

Def delete_data (self, table):

'' remove data from the database table''

Cur = self.conn.cursor ()

Sql = "delete from {} where name='myName'" .format (table)

Cur.execute (sql)

Self.conn.commit ()

Def get_fields (self,table):

'get the fields of the specified table''

Cur = self.conn.cursor ()

Sql = "SELECT * FROM {} LIMIT 1" .format (table)

Cur.execute (sql)

V = cur.description

Zds = [I [0] for i in v]

Self.conn.commit ()

Return zds

Def unique (self,table,*fields):

'' unique setting

Table: table name, fields: list of field names;''

Cur = self.conn.cursor ()

If len (fields) = = 1:

Sql = "ALTER TABLE {} ADD unique (" .format (table))

Else:

Sql = "ALTER TABLE {} ADD UNIQUE KEY (" .format (table))

For i in fields:

Sql + = I

If I! = fields [- 1]:

Sql + =','

Else:

Sql + =')'

Try:

Cur.execute (sql)

Except Exception as exc:

Print (exc)

Else:

Self.conn.commit ()

Def closeSql (self):

'close the database connection'

Self.conn.close ()

II. MongoDB database

1. Install mongodb:

Go to the MongoDB official website to download the corresponding version of the installation package: https://www.mongodb.com/download-center?jmp=nav#community

1. Install MongoDB to disk C or disk D.

2. Set up a data\ db folder on disk C as the storage path for data files, and establish a data\ log folder to store log files.

3. Installation service: cmd goes to the bin directory of MongoDB, and execute: mongod-- dbpath "C:\ data\ db"?-- logpath "C:\ data\ log\ log.txt"?-- install-serviceName "MongoDB"

4. Enable the service: net start MongoDB

Note: sometimes it fails to enable the service because the MongoDB is not closed normally. Try to delete the mongod.lock under C:\ data\ db and start the service again.

Mongod-dbpath "C:\ data\ db"?-logpath "C:\ data\ log\ log.txt"?-install-serviceName "MongoDB"

2. Install pymongo:

Pip install pymongo

Import pymongo

Import datetime

Class Mongodb:

"" simple operation of Python MangoDB ""

Def _ init__ (self):

# 1. Establish a connection

# client = pymongo.MongoClient ('localhost',27017) # the first method

Client = pymongo.MongoClient ('mongodb://localhost:27017') # second method

# 2. Obtain the database

# db = client.db_name # the first method

Db = client ['db_name'] # the second method

# 3. Get a collection

# self.collection = db.table # the first method

Self.collection = db ['table'] # the second method

Def insert_data (self):

'' insert document''

# name: name; age: age; datetime: storage time

User = {

"name": "myName"

"age": 18

"datetime": datetime.datetime.utcnow ()

}

# after insertion, if there is no key value of _ id in the document, the system will automatically add _ id;, where user can be a list of multiple user

User_id = self.collection.insert (user)

Return user_id

Def find_one_data (self):

'' query a document''

Data = self.collection.find_one ({"name": "myName"}) # query the name for myName

Return data

Def find_data (self):

'' query multiple documents''

# data = [d for d in self.collection.find ()] # query all

Data = [d for d in self.collection.find ({"name": "myName"})] # query all documents for the specified name

Return data

Def find_limit_data (self):

'' query data according to conditions:

The conditional operators in MongoDB are:

(>) greater than-$gt

(=) greater than or equal to-$gte

(

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.