import pymysql class mySql: def __init__(self, host='localhost', port=3306, user='root', passwd='123456', db='test'): '''由于MySQL在本地运行,所以传入的是localhost。如果MySQL在远程运行,则传入其公网IP地址 sss = memory('localhost', 3306, 'root', '123456', 'test') self._host = host self._port = port self._user = user self._passwd = passwd self._db = db self._conn='' self._cur='' def conndata(self): conn ='' try: conn = pymysql.connect( host=self._host, port=self._port, user=self._user, passwd=self._passwd, db=self._db ) except: print('数据库连接失败') self._conn=conn return conn def exesql(self,sql): self._cur = self._conn.cursor() self._cur.execute(sql) result = self._cur.fetchall() return result def close(self): self._cur.close() self._conn.close() #简单复习SQL语句 """ CREATE TABLE IF NOT EXISTS students (id VARCHAR (255) NOT NULL ,name VARCHAR(255) NOT NULL,age INT NOT NULL PRIMARY KEY(id)) INSERT INTO students(id,name,age) values(%s,%s,%s) // cursor.execute(sql, (id, user, age)) UPDATE students SET age =%s WHERE name =%s ON DUPLICATE KEY UPDATE // duplicate 如果主键已经存在,就执行更新操作。 DELETE FROM {table} WHERE {condition} SELECT* FROM students WHERE age >=20 四大特性 : 原子性 一致性,持久性,隔离性 try: cursor.execute(sql) db.commit() #提交 except: db.rollback() # 回滚 """ ''' SQL语句会根据字典动态构造,元组也动态构造,这样才能实现通用的插入方法 ''' data={ 'id':'20120001', 'name':'Bob', 'age':20 } table='students' keys=','.join(data.keys()) values=','.join(['%s']*len(data)) sql='INSERT INTO{table}({keys}) VALUES ({values}).format(table=table,keys=keys,values=values)' #字符串的format()方法将表名、字段名和占位符构造出来。最终的SQL语句就被动态构造成了INSERT INTO students(id, name, age) VALUES (%s, %s, %s) # try: # if cursor.execute(sql,tuple(data.values())): # print('Successful') # db.commit() # except: # print("Failed") # db.rollback() # db.close() ## 更新 # data = { # 'id': '20120001', # 'name': 'Bob', # 'age': 21 # } # # table = 'students' # keys = ', '.join(data.keys()) # values = ', '.join(['%s'] * len(data)) # # sql = 'INSERT INTO {table}({keys}) VALUES ({values}) ON DUPLICATE KEY UPDATE'.format(table=table, keys=keys, # values=values) # update = ','.join([" {key} = %s".format(key=key) for key in data]) # sql += update ##INSERT INTO students(id, name, age) VALUES (%s, %s, %s) ON DUPLICATE KEY UPDATE id = %s, name = %s, age = %s # try: # if cursor.execute(sql, tuple(data.values()) * 2)://第二个参数元组就需要乘以2变成原来的2倍。 # print('Successful') # db.commit() # except: # print('Failed') # db.rollback() # db.close() ''' sql = 'SELECT * FROM students WHERE age >= 20' try: cursor.execute(sql) print('Count:', cursor.rowcount) one = cursor.fetchone() print('One:', one) results = cursor.fetchall() print('Results:', results) print('Results Type:', type(results)) for row in results: print(row) except: print('Error') fetchall()方法不是获取所有数据吗?这是因为它的内部实现有一个偏移指针用来指向查询结果, 最开始偏移指针指向第一条数据,取一次之后,指针偏移到下一条数据,这样再取的话,就会取到下一条数据了。 我们最初调用了一次fetchone()方法,这样结果的偏移指针就指向下一条数. 用while循环加fetchone()方法来获取所有数据,而不是用fetchall()全部一起获取出来。 fetchall()会将结果以元组形式全部返回,如果数据量很大,那么占用的开销会非常高 sql = 'SELECT * FROM students WHERE age >= 20' try: cursor.execute(sql) print('Count:', cursor.rowcount) row = cursor.fetchone() while row: print('Row:', row) row = cursor.fetchone() except: print('Error') '''
python--Mysql
最新推荐文章于 2024-01-19 09:25:53 发布