Home >Backend Development >Python Tutorial >Three articles to help you figure out how to learn mysql database and mysql cross-database query

Three articles to help you figure out how to learn mysql database and mysql cross-database query

Tomorin
TomorinOriginal
2018-08-16 13:54:422779browse

Previously we have conducted a deep level of study on mysql database learning through "Three articles to help you learn how to learn mysql database and create tables in the mysql library"Now Let’s carry out mysql database learningThe most critical knowledge point-mysql cross-database query.

##Before learning mysql cross-database query, we must first learn the database insertion operation:

The following example uses the SQL INSERT statement to insert records into the table EMPLOYEE:

#!/usr/bin/python
# -*- coding: UTF-8 -*-

import MySQLdb

# 打开数据库连接
db = MySQLdb.connect("localhost", "testuser", "test123", "TESTDB", charset='utf8' )

# 使用cursor()方法获取操作游标 
cursor = db.cursor()

# SQL 插入语句
sql = """INSERT INTO EMPLOYEE(FIRST_NAME,
         LAST_NAME, AGE, SEX, INCOME)
         VALUES ('Mac', 'Mohan', 20, 'M', 2000)"""

try:
   # 执行sql语句
   cursor.execute(sql)
   # 提交到数据库执行
   db.commit()
except:
   # Rollback in case there is any error
   db.rollback()
# 关闭数据库连接
db.close()

Database query operation

Python To query Mysql, use the fetchone() method to obtain a single piece of data, and use the fetchall() method to obtain multiple pieces of data.

1.fetchone(): This method gets the next query result set. The result set is an object.

2.fetchall(): Receive all the returned result rows.

3.rowcount: This is a read-only attribute and returns the effect after executing the execute() method. number of rows.

Example:

Query all data with a salary field greater than 1000 in the EMPLOYEE table:

#!/usr/bin/python
# -*- coding: UTF-8 -*-

import MySQLdb

# 打开数据库连接
db = MySQLdb.connect("localhost", "testuser", "test123", "TESTDB", charset='utf8' )

# 使用cursor()方法获取操作游标 
cursor = db.cursor()

# SQL 查询语句
sql = "SELECT * FROM EMPLOYEE \
       WHERE INCOME > '%d'" % (1000)
try:
   # 执行SQL语句
   cursor.execute(sql)
   # 获取所有记录列表
   results = cursor.fetchall()
   for row in results:
      fname = row[0]
      lname = row[1]
      age = row[2]
      sex = row[3]
      income = row[4]
      # 打印结果
      print "fname=%s,lname=%s,age=%d,sex=%s,income=%d" % \       
            (fname, lname, age, sex, income )
except:
   print "Error: unable to fecth data"
   
关闭数据库连接
db.close()


The above is the detailed content of Three articles to help you figure out how to learn mysql database and mysql cross-database query. For more information, please follow other related articles on the PHP Chinese website!

Statement:
The content of this article is voluntarily contributed by netizens, and the copyright belongs to the original author. This site does not assume corresponding legal responsibility. If you find any content suspected of plagiarism or infringement, please contact admin@php.cn