python和sql_Python与sql的基础操作教程

Python与sql的基础操作教程

１、Python与Mysql之间的关系

class Pymysqltest:

'''mysql test'''

def __init__(self):

self.con=pymysql.connect(user='root',password='123123',db='test')

self.cur=self.con.cursor()

self.data=[]

self.cur.execute("drop table gupiao")

self.cur.execute("create table gupiao(Date char(10) primary key not null,Open char(5),High char(5),Low char(5),Close char(5),Volume char(12),Adjclose char(5))")

self.con.commit()

with open("/home/sw/data/testdata/test.csv", 'r') as f:

data = csv.reader(f)

next(data)

for row in data:

self.data.append(tuple(row[1:]))

def Rdata(self): #增加数据

self.cur.executemany("insert into gupiao(Date,Open,High,Low,Close,Volume,Adjclose) values(%s,%s,%s,%s,%s,%s,%s)",self.data)

self.con.commit()

def Sdata(self): #查询数据

self.cur.execute("select * from gupiao")

for row in self.cur:

print("查询的结果:",row)

def Updedata(self): #更改数据

self.cur.execute("select * from gupiao where Date='2017-05-01'")

for row in self.cur:

print("修改前查询的结果:",row)

self.cur.execute("update gupiao set Open=100 where Date='2017-05-01'")

self.cur.execute("select * from gupiao where Date='2017-05-01'")

for row in self.cur:

print("修改前查询的结果:", row)

for row in self.cur:

print("修改后查询结果为:",row)

self.con.commit()

def Ddate(self): #删除数据

self.cur.execute("delete from gupiao where Date='2017-05-01'")

self.con.commit()

def Closedata(self): #关闭数据库连接

self.con.close()

2、Python与sqlite3之间的关系

class Sqlite:

'''sqlite test'''

def __init__(self,db_name='/home/sw/data/ipython test/test.db'):

self.con=connect(db_name)

self.cur=self.con.cursor()

self.data=[]

self.cur.execute("drop table gupiao")

self.cur.execute('create table gupiao(Date text,Open text,High text,Low text,Close text,Volume text,Adjclose text)')

with open("/home/sw/data/testdata/test.csv", 'r') as f:

data = csv.reader(f)

next(data)

for row in data:

self.data.append(tuple(row[1:]))

def Rdata(self): #增加数据

self.cur.executemany(

"insert into gupiao(Date,Open,High,Low,Close,Volume,Adjclose) values(?,?,?,?,?,?,?)",self.data)

self.con.commit()

def Sdata(self): #查询数据

self.cur.execute("select * from gupiao")

for row in self.cur:

print("查询的结果:",row)

def Updedata(self): #更改数据

self.cur.execute("select * from gupiao where Date='2017-05-01'")

for row in self.cur:

print("修改前查询的结果:",row)

self.cur.execute("update gupiao set Open=100 where Date='2017-05-01'")

self.cur.execute("select * from gupiao where Date='2017-05-01'")

for row in self.cur:

print("修改前查询的结果:", row)

for row in self.cur:

print("修改后查询结果为:",row)

self.con.commit()

def Ddate(self): #删除数据

self.cur.execute("delete from gupiao where Date='2017-05-01'")

self.con.commit()

def Closedata(self): #关闭数据库连接

self.con.close()

３、Python与pandas sql之间的关系

class pdtosql:

'''pandas to sql test'''

def __init__(self):

self.con=pymysql.connect(user='root',password='123123',db='test',charset='utf8')

self.cur=self.con.cursor()

self.cur.execute("drop table gupiao1")

def Rdate(self): #增加数据

data=pd.read_csv("/home/sw/data/testdata/test.csv").astype(str)

data.iloc[:,1:].to_sql('gupiao1',self.con,flavor='mysql')

def Sdata(self): #查询数据

data1=pd.read_sql('select* from gupiao1',self.con,index_col='Date')

return data1

全部代码如下所示：

__Author__ = '随心'

__Date__="2017-05-03"

import pymysql

import csv

from sqlite3 import connect

import pandas as pd

class Pymysqltest:

'''mysql test'''

def __init__(self):

self.con=pymysql.connect(user='root',password='123123',db='test')

self.cur=self.con.cursor()

self.data=[]

self.cur.execute("drop table gupiao")

self.cur.execute("create table gupiao(Date char(10) primary key not null,Open char(5),High char(5),Low char(5),Close char(5),Volume char(12),Adjclose char(5))")

self.con.commit()

with open("/home/sw/data/testdata/test.csv", 'r') as f:

data = csv.reader(f)

next(data)

for row in data:

self.data.append(tuple(row[1:]))

def Rdata(self): #增加数据

self.cur.executemany("insert into gupiao(Date,Open,High,Low,Close,Volume,Adjclose) values(%s,%s,%s,%s,%s,%s,%s)",self.data)

self.con.commit()

def Sdata(self): #查询数据

self.cur.execute("select * from gupiao")

for row in self.cur:

print("查询的结果:",row)

def Updedata(self): #更改数据

self.cur.execute("select * from gupiao where Date='2017-05-01'")

for row in self.cur:

print("修改前查询的结果:",row)

self.cur.execute("update gupiao set Open=100 where Date='2017-05-01'")

self.cur.execute("select * from gupiao where Date='2017-05-01'")

for row in self.cur:

print("修改前查询的结果:", row)

for row in self.cur:

print("修改后查询结果为:",row)

self.con.commit()

def Ddate(self): #删除数据

self.cur.execute("delete from gupiao where Date='2017-05-01'")

self.con.commit()

def Closedata(self): #关闭数据库连接

self.con.close()

class Sqlite:

'''sqlite test'''

def __init__(self,db_name='/home/sw/data/ipython test/test.db'):

self.con=connect(db_name)

self.cur=self.con.cursor()

self.data=[]

self.cur.execute("drop table gupiao")

self.cur.execute('create table gupiao(Date text,Open text,High text,Low text,Close text,Volume text,Adjclose text)')

with open("/home/sw/data/testdata/test.csv", 'r') as f:

data = csv.reader(f)

next(data)

for row in data:

self.data.append(tuple(row[1:]))

def Rdata(self): #增加数据

self.cur.executemany(

"insert into gupiao(Date,Open,High,Low,Close,Volume,Adjclose) values(?,?,?,?,?,?,?)",self.data)

self.con.commit()

def Sdata(self): #查询数据

self.cur.execute("select * from gupiao")

for row in self.cur:

print("查询的结果:",row)

def Updedata(self): #更改数据

self.cur.execute("select * from gupiao where Date='2017-05-01'")

for row in self.cur:

print("修改前查询的结果:",row)

self.cur.execute("update gupiao set Open=100 where Date='2017-05-01'")

self.cur.execute("select * from gupiao where Date='2017-05-01'")

for row in self.cur:

print("修改前查询的结果:", row)

for row in self.cur:

print("修改后查询结果为:",row)

self.con.commit()

def Ddate(self): #删除数据

self.cur.execute("delete from gupiao where Date='2017-05-01'")

self.con.commit()

def Closedata(self): #关闭数据库连接

self.con.close()

class pdtosql:

'''pandas to sql test'''

def __init__(self):

self.con=pymysql.connect(user='root',password='123123',db='test',charset='utf8')

self.cur=self.con.cursor()

self.cur.execute("drop table gupiao1")

def Rdate(self): #增加数据

data=pd.read_csv("/home/sw/data/testdata/test.csv").astype(str)

data.iloc[:,1:].to_sql('gupiao1',self.con,flavor='mysql')

def Sdata(self): #查询数据

data1=pd.read_sql('select* from gupiao1',self.con,index_col='Date')

return data1

if __name__=='__main__':

test=Pymysqltest()

test.Rdata()

test.Sdata()

test.Updedata()

test.Ddate()

test.Closedata()

test1=Sqlite()

test1.Rdata()

test1.Sdata()

test1.Updedata()

test1.Ddate()

test1.Closedata()

test2=pdtosql()

test2.Rdate()

test2.Sdata()你的想法再精彩，那是想法的价值

而你的价值，永远体现在行动之中

如果还停留在想的价值中，请赶快行动，

如果你学习还停止在原处，请咨询我帮助你开始学好python你需要一个良好的环境，一个优质的开发交流群，群里都是那种相互帮助的人才是可以的，我有建立一个python学习交流群，在群里我们相互帮助，相互关心，相互分享内容，这样出问题帮助你的人就比较多，群号是301，还有056，最后是051，这样就可以找到大神聚合的群，如果你只愿意别人帮助你，不愿意分享或者帮助别人，那就请不要加了，你把你会的告诉别人这是一种分享。如果你看了觉得还可以的麻烦给我点个赞谢谢

。