1. python将数据导到MySQL的时候,按时间分表,创建了一个触发器,为什么有这个错误提示
MySQL中有个ifnull函数,可以帮你搞定。
ifnull(expr1,expr2)表示如果expr1不是null,则返回expr1,否则返回expr2,针对题主的问题,可以使用 ifnull(列名,0)
2. python3使用pymysql模块往mysql插入数据时报错
1234>>> li = [1,2,4,5]>>> li.insert(2,3)>>> li[1, 2, 3, 4, 5]list.insert(index,var),第一个参数是列表索引,指向要插入的位置;第二个参数是要插入的元素
3. python操作数据库mysql数据库出现错误奇怪?望高人指点?
给你我写的python mysql扩展吧
#-*- coding: utf-8 -*-#mysql by final v1.03efrom warnings import filterwarningsimport MySQLdbfilterwarnings('ignore', category = MySQLdb.Warning)#from warnings import resetwarnings#resetwarnings()class mysql: def __init__(self,host,user,passwd,db,timeout=5,language="utf8"): self.host=host self.user=user self.passwd=passwd self.db=db self.sql="" self.timeout=timeout try: self.conn=MySQLdb.connect(host=self.host,user=self.user,passwd=self.passwd,db=self.db)#,connect_timeout=self.timeout) except: print "MySQLdb connect error" return else: pass self.language(language) self.cursor=self.conn.cursor(MySQLdb.cursors.DictCursor) #self.cursor=self.conn.cursor(cursorclass=MySQLdb.cursors.DictCursor) self.cursor.execute("set names '%s'"%language) self.conn.text_factory=str self.fieldsarr=[] self.pagesize=20 self.page=0 self.usepage=0 def language(self,language): self.conn.set_character_set(language) def fields(self): return self.fieldsarr def setpagesize(self,pagesize=0): self.pagesize=pagesize def getpageinfo(self): ifre=re.search("select ([0-9a-zA-Z|,| |']*) from ([0-9a-zA-Z|,|'|_| ]*)",self.pagesql) maxcount=self.fetch("select count(*) from %s"%ifre.group(2))[0] return [maxcount,int(maxcount/self.pagesize)] def query(self,sql): #print "!3" if self.usepage==1: sql="%s limit %s,%s"%(sql,(self.page-1)*self.pagesize,self.pagesize) self.usepage=0 self.sql=sql #print sql self.cursor.execute(sql) def execute(self,sql): self.sql=sql self.cursor.execute(sql) self.conn.commit() def fetchall(self,sql="",page=-1): if page>=0: self.page=page+1 self.usepage=1 self.pagesql=sql if sql: self.query(sql) #self.fieldsarr=["%s"%x[0] for x in self.cursor.description] self.fieldsarr=self.cursor.description return self.cursor.fetchall() def fetch(self,sql=""): #print "222" if sql: self.query(sql) #self.fieldsarr=["%s"%x[0] for x in self.cursor.description] #print 0 self.fieldsarr=self.cursor.description #print 1 mfetchall=self.cursor.fetchall() #print 3 if mfetchall: return mfetchall[0] else: return [] def convstr(self,s,fun=None): if type(s) in (type(u""),type("")): if fun: #print "k fun" return fun(s,139) else: #print "no str" return str(s).replace("'","''") else: #print "num" return s def tosql(self,sql): return self.conn.escape_string(sql) def addnew(self,tablename,data={},fun=None,commit=True): if fun: sql="insert into %s (%s) values (%s)"%(tablename,",".join(["`%s`"%y for y in data]),",".join(["'%s'"%self.convstr(data[y],fun) for y in data])) else: sql="insert into %s (%s) values (%s)"%(tablename,",".join(["`%s`"%y for y in data]),",".join(["'%s'"%str(data[y]).replace("'","''") for y in data])) sql=sql.replace("'fdm:datetime'","now()") sql=sql.replace("'fdm:time'","UNIX_TIMESTAMP()") #print sql self.cursor.execute(sql) self.insertid=int(self.conn.insert_id()) if commit==True: self.conn.commit() def delete(self,tablename,ids=[]): sql="delete from %s where id in (%s)"%(tablename,",".join(["%s"%y for y in ids])) self.cursor.execute(sql) self.conn.commit() def update(self,tablename,data={},where=""): sql="update %s set %s"%(tablename,",".join(["`%s`='%s'"%(x,str(data[x]).replace("'","''")) for x in data])) if where: sql="%s where %s"%(sql,where) sql=sql.replace("'fdm:datetime'","now()") sql=sql.replace("'fdm:time'","UNIX_TIMESTAMP()") #print sql self.cursor.execute(sql) self.conn.commit() def __del__(self): print "close mysql"; self.cursor.close() self.conn.close() def insert_id(self): self.conn.insert_id() def close(self): self.__del__()
4. python批量导入mysql数据库出现报错,出现一个字段不能为空
你先把 insert_sql 一个一个打印出来看下到底是哪个问题。
还有一种可能就是文本文件最後一行是没有内容的(即留空了)
5. python爬虫爬下来的数据怎么导入到MySQL
去装一个 pymsql包
然后
import pymysql #导入pymsql模块#链接数据库,注意port是int型,不是str,所以不要用引号conn = pymysql.connect( user='root', password='root', host='127.0.0.1', port=3306, database='test_demo', use_unicode=True, charset="utf8" )#获取游标cursor = conn.cursor()#插入数据,注意看有变量的时候格式cursor.execute("INSERT INTO tieba_user(`uuid`,`user_name`,`user_rank`,`user_level`,`level_status`,`tieba_name`) VALUES (%s,%s,%s,%s,%s,%s)", (user_id, user_name, user_rank, user_level, level_statu, tieba_name))#提交conn.commit()#关闭连接cursor.close()类似这样
6. MySQL-python连接MySQL数据库问题,总是抛异常。
不要刚开始学多线程编程就这样玩。connection 和 cursor 都不是线程安全的。
如果测试环境用多个线程,每个线程要在线程里面获取自己的 connection,然后从这个connection 获取 cursor.
如果生产环境用多个线程,建议使用线程安全的连接池。
7. 怎么用python将文本文件里的数据导入到mysql数据库
with open("fileinfo.txt", "rt") as handle: datas = [ln.split('\t') for ln in handle]conn = MySQLdb.connect(host='localhost', user='mysql', passwd='', db='db')curr = conn.cursor()curr.executemany("insert into tb values (?, ?, ?)", datas)conn.commit()
8. Python进行MySQL数据库更新出现TypeError: not all arguments converted during string formatting错误?
你拼接 的"update ...%s " 语句的所有%s ,后面给的参数个数不匹配
前面有几个%s,后面就得给几个参数 ,仔细检查哈