# 这是sql创建DB，table，删除，insert的学习


import pyodbc
DBcnxn = pyodbc.connect(DSN = 'TEST',autocommit=True)
DBcursor = DBcnxn.cursor()

createDBcommand = \
'''
    if not exists(select * from sys.databases where name = N'BT')
    BEGIN
        CREATE database BT;
    END
'''

createTableCommand = """
    IF NOT EXISTS (SELECT * FROM BT.INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = N'BTrecords')
    begin
        create table BT.[dbo].BTrecords(
            btdate VARCHAR(255),
            positionList VARCHAR(255),
            cash VARCHAR(255),
            positionListValue VARCHAR(255),
            totalValue VARCHAR(255),
            strategyID VARCHAR(255));
    end
    """
#DBcursor.execute(createCommand)

dropTableCommand = """
    IF EXISTS (SELECT * FROM BT.INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = N'BTrecords')
    begin
        drop table BT.[dbo].BTrecords;
    end
"""
#DBcursor.execute(dropCommand)

DBcursor.execute(createDBcommand + createTableCommand )
#print(DBcursor.fetchall())

#insertCommand = "Insert into BT.[dbo].BTrecords (btdate,positionList,cash,positionListValue,totalValue,strategyID) values (%s,%0.2f,%0.2f,%0.2f,%0.2f,%s);"%\
('2015-05-05',2,1222,32,12312,'1000')
insertCommand = "INSERT INTO BT.dbo.BTrecords VALUES (%s,%0.0f,%0.2f,%0.2f,%0.2f,%s);"%('2015-05-05',2,1222,32,12312,'1000')

print(insertCommand)
DBcursor.execute(insertCommand)
DBcursor.execute('select * from BT.dbo.BTrecords')
raw = DBcursor.fetchall()
print(raw)



