Python 中的 MySQLdb 连接
Mysql是最广泛使用的开源数据库之一。Python提供连接到此数据库的方法,并使用该数据库存储和检索数据。
安装 pymysql
根据使用的 Python 环境,pymysql 软件包可以通过以下方法之一进行安装。
# From python console pip install pymysql #Using Anaconda conda install -c anaconda pymysql # Add modules using any python IDE pymysql
连接到 MySQL
现在,我们可以使用以下代码连接到 MySQL 环境。连接后,我们将找出数据库的版本。
Learn Python in-depth with real-world projects through our Python certification course. Enroll and become a certified expert to boost your career.
示例
import pymysql # Open database connection db = pymysql.connect("localhost","testuser","test123","TESTDB" ) # prepare a cursor object using cursor() method cursor = db.cursor() # execute SQL query using execute() method. cursor.execute("SELECT VERSION()") # Fetch a single row using fetchone() method. data = cursor.fetchone() print ("Database version : %s " % data) # disconnect from server db.close()
输出
运行以上代码得到以下结果:
Database version : 8.0.19
执行数据库命令
为了执行数据库命令,我们创建了一个数据库游标和一个 SQL 查询以传递到该游标中。然后,我们使用游标的 execute 方法来从游标执行中获取结果。
示例
import pymysql # Open database connection db = pymysql.connect("localhost","username","paswd","DBname" ) # prepare a cursor object using cursor() method cursor = db.cursor() sql = "SELECT * FROM EMPLOYEE \ WHERE INCOME > '%d'" % (1000) try: # Execute the SQL command cursor.execute(sql) # Fetch all the rows in a list of lists. results = cursor.fetchall() for row in results: fname = row[0] lname = row[1] age = row[2] sex = row[3] income = row[4] # Now print fetched result print "fname=%s,lname=%s,age=%d,sex=%s,income=%d" % \ (fname, lname, age, sex, income ) except: print "Error: unable to fecth data" # disconnect from server db.close()
输出
运行以上代码得到以下结果:
fname = Jack, lname = Ma, age = 31, sex = M, income = 12000
广告