instance_name="ocp01" #'"select * from instances where display_name="nfv_instance 00 011"' engine = db.create_engine('mysql://*****:*****@10.255.26.100:3306/nova') connection = engine.connect() metadata = db.MetaData() nova_instances=db.Table('instances', metadata, autoload=True, autoload_with=engine) instances_uuid=connection.execute(db.select([nova_instances.columns.uuid]).where(nova_instances.columns.display_name==instance_name)).fetchall() print(instances_uuid) #select numa_topology from instance_extra where instance_uuid="15a468f0-82eb-4cae-bca8-48bddf03e711" nova_instance_extra=db.Table('instance_extra', metadata, autoload=True, autoload_with=engine) instance_info=connection.execute(db.select([nova_instance_extra.columns.numa_topology]).where(nova_instances.columns.instance_uuid==instances_uuid)).fetchall() instance_cell=instance_info["nova_object.data"] instance_cpu=[] for numa in instance_cell["cells"]: cpuset=[x for x in numa['nova_object.data']["cpuset"]] instance_cpu+=cpuset print(instance_cpu )
def get_metadef_tags_table(meta): return sqlalchemy.Table('metadef_tags', meta, autoload=True)
try: study = db_session.query(Study).filter_by(name=key).one() except orm.exc.NoResultFound: raise KeyError study.__parent__ = self return study # Configured forms for the study study_schema_table = sa.Table( 'study_schema', StudiesModel.metadata, sa.Column('study_id', sa.Integer(), sa.ForeignKey('study.id', name='fk_study_schema_study_id', ondelete='CASCADE'), primary_key=True), sa.Column('schema_id', sa.Integer(), sa.ForeignKey(datastore.Schema.id, name='fk_study_schema_schema_id', ondelete='CASCADE'), primary_key=True)) # Configured forms for the cycle cycle_schema_table = sa.Table( 'cycle_schema', StudiesModel.metadata, sa.Column('cycle_id', sa.Integer(), sa.ForeignKey('cycle.id', name='fk_cycle_schema_cycle_id', ondelete='CASCADE'),
from data.database.all_for_session import SqlAlchemyBase import sqlalchemy # Промежуточная таблица между пользователем и олимпиадой user_to_olimpiad = sqlalchemy.Table( 'user_to_olimpiad', SqlAlchemyBase.metadata, sqlalchemy.Column('user', sqlalchemy.Integer, sqlalchemy.ForeignKey('users.id')), sqlalchemy.Column('olimpiad', sqlalchemy.Integer, sqlalchemy.ForeignKey('olimpiads.id')))
def get_metadef_namespace_resource_types_table(meta): return sqlalchemy.Table('metadef_namespace_resource_types', meta, autoload=True)
import sqlalchemy as db SqlEventLogStorageMetadata = db.MetaData() SqlEventLogStorageTable = db.Table( 'event_logs', SqlEventLogStorageMetadata, db.Column('id', db.Integer, primary_key=True, autoincrement=True), db.Column('run_id', db.String(255)), db.Column('event', db.Text, nullable=False), db.Column('dagster_event_type', db.Text), db.Column('timestamp', db.types.TIMESTAMP), )
import sqlalchemy as sa metadata = sa.MetaData() # Users users = sa.Table( "users", metadata, sa.Column("id", sa.Integer, nullable=False), sa.Column("login", sa.String(256), nullable=False), sa.Column("passwd", sa.String(256), nullable=False), sa.Column( "is_superuser", sa.Boolean, nullable=False, server_default="FALSE" ), sa.Column("disabled", sa.Boolean, nullable=False, server_default="FALSE"), # indices sa.PrimaryKeyConstraint("id", name="user_pkey"), sa.UniqueConstraint("login", name="user_login_key"), ) # Permissions permissions = sa.Table( "permissions", metadata, sa.Column("id", sa.Integer, nullable=False), sa.Column("user_id", sa.Integer, nullable=False), sa.Column("perm_name", sa.String(64), nullable=False), # indices sa.PrimaryKeyConstraint("id", name="permission_pkey"), sa.ForeignKeyConstraint(
import datetime import sqlalchemy as sql metadata = sql.MetaData() summary = sql.Table('summary', metadata, sql.Column('id', sql.Integer, primary_key=True), sql.Column('result', sql.Boolean), sql.Column('build_date', sql.String)) summary_item = sql.Table( 'summary_item', metadata, sql.Column('id', sql.Integer, primary_key=True), sql.Column('repo_name', sql.String), sql.Column('exception', sql.String), sql.Column('commit_id', sql.String), sql.Column('image_id', sql.String), sql.Column('source_desc', sql.String), sql.Column('tag', sql.String), sql.Column('summary_id', None, sql.ForeignKey('summary.id'))) class SummaryV2(object): def __init__(self, engine, summary_id, errorlogs=None): self.summary_id = summary_id self._engine = engine self.errorlogs = errorlogs def handle_build_result(self, exc, repo, version, img_id, build_result): c = self._engine.connect() if exc and self.errorlogs: if isinstance(build_result, list): build_result = '\n'.join(build_result) elif not isinstance(build_result, str): build_result = str(build_result)
__tablename__ = 'macroarea' name = sa.Column(sa.Enum(*sorted(MACROAREA)), primary_key=True) def __repr__(self): return '<%s %r>' % (self.__class__.__name__, self.name) languoids = sa.orm.relationship('Languoid', secondary='languoid_macroarea', order_by='Languoid.id', back_populates='macroareas') languoid_macroarea = sa.Table( 'languoid_macroarea', _backend.Model.metadata, sa.Column('languoid_id', sa.ForeignKey('languoid.id'), primary_key=True), sa.Column('macroarea_name', sa.ForeignKey('macroarea.name'), primary_key=True)) class Country(_backend.Model): __tablename__ = 'country' id = sa.Column(sa.String(2), sa.CheckConstraint('length(id) = 2'), primary_key=True) name = sa.Column(sa.Text, sa.CheckConstraint("name != ''"), nullable=False, unique=True)
import sqlalchemy import pymysql from pprint import pprint # run a select statement on a db table engine = sqlalchemy.create_engine( 'mysql+pymysql://root:add_pwd@localhost/sakila') connection = engine.connect() metadata = sqlalchemy.MetaData() actor = sqlalchemy.Table('actor', metadata, autoload=True, autoload_with=engine) query = sqlalchemy.select([actor]) result_proxy = connection.execute(query) #result_set = result_proxy.fetchall() result_set = result_proxy.fetchmany(5) pprint(result_set)
import sqlalchemy as sa from .base import metadata board = sa.Table('board', metadata, sa.Column('row_id', sa.Integer, primary_key=True), sa.Column('name', sa.String(length=32), unique=True))
import os import databases import sqlalchemy DATABASE_URL = os.getenv('DATABASE_URL') or "sqlite:////db.db" database = databases.Database(DATABASE_URL) metadata = sqlalchemy.MetaData() urls = sqlalchemy.Table( "urls", metadata, sqlalchemy.Column("id", sqlalchemy.Integer, primary_key=True), sqlalchemy.Column("url", sqlalchemy.String), sqlalchemy.Column("short_code", sqlalchemy.String, unique=True), ) engine = sqlalchemy.create_engine(DATABASE_URL, #connect_args={"check_same_thread": False} ) metadata.create_all(engine)
TEMP_F_0500 = Column(Float) TEMP_F_0600 = Column(Float) TEMP_F_0700 = Column(Float) TEMP_F_0800 = Column(Float) TEMP_F_0900 = Column(Float) TEMP_F_1000 = Column(Float) TEMP_F_1100 = Column(Float) TEMP_F_1200 = Column(Float) TEMP_F_1300 = Column(Float) TEMP_F_1400 = Column(Float) TEMP_F_1500 = Column(Float) TEMP_F_1600 = Column(Float) TEMP_F_1700 = Column(Float) TEMP_F_1800 = Column(Float) TEMP_F_1900 = Column(Float) TEMP_F_2000 = Column(Float) TEMP_F_2100 = Column(Float) TEMP_F_2200 = Column(Float) TEMP_F_2300 = Column(Float) def __repr__(self): return f"id={self.id}, name={self.name}" Base.metadata.create_all(engine) metadata = MetaData(bind=engine) metadata.reflect() table = sqlalchemy.Table('cz_2010_usc', metadata, autoload=True) conn.execute(table.delete()) conn.execute(table.insert(), data_dict)
def downgrade(migrate_engine): meta = sql.MetaData() meta.bind = migrate_engine region_table = sql.Table(_REGION_TABLE_NAME, meta, autoload=True) region_table.drop_column('url')
def _table(*args, **kwargs): kwargs = kwargs.copy() kwargs['mysql_engine'] = 'InnoDB' return sqlalchemy.Table(*args, **kwargs)
def Get_Twitter_Data_As_Pandas(C_Key, C_Secret, A_Token, A_Token_Secret, Max_Tweets=100, Filters=["bitcoin"], Table_Name="tmp_tweets", New_Table_Columns=None, Query=None, Tweet_Data_Parts=None, Temporary=True, *args, **kwargs): """ C_Key, String - Twitter Consumer Key C_Secret, String - Twitter Consumer Secret A_Token, String - Twitter Access Token A_Token_Secret, String - Twitter Access Token Secret Max_Tweets= 100, INT - Number of tweets to extract Filters = None, List(String) - What words to filter on Default - ["Trump"] Table_Name = None, String - The name of your new table (default is tmp_tweets) New_Table_Columns = "(date DATETIME, username VARCHAR(20), tweet VARCHAR(280))", List(String) - SQL format tuple of string pairs for column name and type e.g. ['time DATETIME', 'age INT(2)']' Query = None, String - SQL query to execute in database table Tweet_Data_Parts = None List(String/List(String)) - Parts of the tweet json (according to twitter) to extract e.g. [{"user":"******"}, text'] is default Time is automatically added in to database Temporary = True, Bool - Store Tweets in the Database temporarily or permanently Default = True """ if not Query: Query = sqlalchemy.select([sqlalchemy.Text(Table_Name)]) else: pass auth = tweepy.OAuthHandler(consumer_key=C_Key, consumer_secret=C_Secret) auth.set_access_token(A_Token, A_Token_Secret) db_connection = MySQLdb.connect("127.0.0.1", "root", "redcard", "tweet_store", charset='utf8mb4') cursor = db_connection.cursor() tweet_add_milestone = int(Max_Tweets / 5) # ## Define a class to listen to the twitter API # If we want to use twitter data and/or a database other than the default then define this custom listener: if Tweet_Data_Parts and New_Table_Columns: class Stream_Listener(tweepy.StreamListener): def __init__(self, api=None, Max_Tweets_=None, Table_Name_=None, New_Table_Columns_=None, Tweet_Data_Parts_=None): super().__init__() self.num_tweets = 0 self.max_tweets = Max_Tweets_ self.table_name = Table_Name_ self.tweet_data_parts = Tweet_Data_Parts_ self.new_table_columns = New_Table_Columns_ # For creating, create table if not default # Below line is hide your warning cursor.execute("SET sql_notes = 0; ") # create table here.... exec_stmt = str( "CREATE TABLE IF NOT EXISTS " + self.table_name + " " + self.new_table_columns + " CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci") cursor.execute(exec_stmt) db_connection.commit() def on_data(self, data): if self.num_tweets < self.max_tweets: all_data = json.loads(data) data_parts = () for part in self.tweet_data_parts: if isinstance(part, str): if part == "created_at": time_created = datetime.datetime.strptime( "%a %b %d %H:%M:%S %z %Y") data_parts += time_created else: data_parts += (all_data[part], ) elif isinstance(part, dict): data_parts += (all_data[part.key()][part.item()], ) else: raise ValueError( "The Listed Tweet_Data_Part was not either of type dict or str" ) num_inserted_vars = len(data_parts) exec_stmt = str( "INSERT INTO " + self.table_name, "(" + str(", ".join(self.new_table_columns)) + ")", "VALUES", str(("%s, " for i in range(num_inserted_vars)))) cursor.execute(exec_stmt, data_parts) db_connection.commit() if self.num_tweets % tweet_add_milestone == 0: print("Successfully added tweet. Number:", self.num_tweets + 1) self.num_tweets += 1 return True else: print("Finished writing to table:", self.table_name) return False def on_error(self, status): print("Error Code:", status) #If we haven't proper;y defined how to create and insert into the database then raise an error elif (not Tweet_Data_Parts) != (not New_Table_Columns): raise ValueError( "Need both New Table Columns and Tweet_Data_Parts to specify alternative tweet data collection" ) #Otherwise use the default listener and database/table else: class Stream_Listener(tweepy.StreamListener): def __init__(self, api=None, Max_Tweets_=None, Table_Name_=None, New_Table_Columns_=None, Tweet_Data_Parts_=None): super().__init__() self.num_tweets = 0 self.max_tweets = Max_Tweets_ self.table_name = Table_Name_ self.tweet_data_parts = Tweet_Data_Parts_ self.new_table_columns = New_Table_Columns_ # For creating, create table if not default # Below line is hide your warning cursor.execute("SET sql_notes = 0; ") # create table here.... exec_stmt = str( "CREATE TABLE IF NOT EXISTS " + self.table_name + " (date DATETIME, username VARCHAR(20), tweet VARCHAR(280)) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci" ) cursor.execute(exec_stmt) db_connection.commit() def on_data(self, data): if self.num_tweets < self.max_tweets: all_data = json.loads(data) tweet = all_data["text"] username = all_data["user"]["screen_name"] cur_time = datetime.datetime.strptime( all_data["created_at"], "%a %b %d %H:%M:%S %z %Y") exec_stmt = str( "INSERT INTO " + self.table_name + " (date, username, tweet) VALUES (%s, %s, %s)") cursor.execute(exec_stmt, (cur_time, username, tweet)) db_connection.commit() if self.num_tweets % tweet_add_milestone == 0: print("Successfully added tweet. Number:", self.num_tweets + 1) self.num_tweets += 1 return True else: print("Finished writing to table:", self.table_name) return False def on_error(self, status): print("Error Code:", status) #Initialise the stream listener listener = Stream_Listener(Max_Tweets_=Max_Tweets, Table_Name_=Table_Name, New_Table_Columns_=New_Table_Columns, Tweet_Data_Parts_=Tweet_Data_Parts) #Authenticate the listener data_stream = tweepy.Stream(auth, listener) #Add filters data_stream.filter(track=Filters) # ## Read the tweets database to Pandas # First create the engine to connect to the database engine = sqlalchemy.create_engine( 'mysql+mysqldb://root:[email protected]/tweet_store') #Set up a metadata object to track table metadata meta_data = sqlalchemy.MetaData() tweet_table = sqlalchemy.Table(Table_Name, meta_data, autoload=True, autoload_with=engine) #Establish the database connection connection = engine.connect() #Create the query and execute it stmt = sqlalchemy.select([tweet_table]) results = connection.execute(stmt).fetchall() df = pd.DataFrame(results) if Temporary: exec_stmt = "DELETE FROM " + Table_Name cursor.execute(exec_stmt) db_connection.commit() connection.execute("FLUSH TABLES " + Table_Name) stmt = "SELECT COUNT(*) FROM " + Table_Name num_rows = connection.execute(stmt).scalar() if num_rows == 0: print("Table Deleted!") else: pass return df
import binascii import hashlib import os import sqlalchemy as sa from sqlalchemy import orm from app.utils.database import ModelBase user_scopes = sa.Table( "userscope", ModelBase.metadata, sa.Column("user_id", ModelBase.id.type, sa.ForeignKey("user.id"), primary_key=True), sa.Column("scope_id", ModelBase.id.type, sa.ForeignKey("scope.id"), primary_key=True), ) class User(ModelBase): email = sa.Column(sa.String(255), nullable=False, index=True, unique=True) password = sa.Column(sa.String(255)) first_name = sa.Column(sa.String(120)) last_name = sa.Column(sa.String(120)) is_active = sa.Column(sa.Boolean, nullable=False, default=True) last_login = sa.Column(sa.DateTime, nullable=True) scopes = orm.relationship("Scope", secondary=user_scopes)
def Stream_Twitter_Data_MYSQL(C_Key, C_Secret, A_Token, A_Token_Secret, Max_Tweets=100, Filters=["Bitcoin"], Table_Name="tmp_tweets", New_Table_Columns=None, Tweet_Data_Parts=None, *args, **kwargs): """ C_Key, String - Twitter Consumer Key C_Secret, String - Twitter Consumer Secret A_Token, String - Twitter Access Token A_Token_Secret, String - Twitter Access Token Secret Max_Tweets= 100, INT - Number of tweets to extract Filters = None, List(String) - What words to filter on Default - ["Trump"] Table_Name = None, String - The name of your new table (default is tmp_tweets) New_Table_Columns = "(date DATETIME, username VARCHAR(20), tweet VARCHAR(280))", List(String) - SQL format tuple of string pairs for column name and type e.g. ['time DATETIME', 'age INT(2)']' Tweet_Data_Parts = None List(String/List(String)) - Parts of the tweet json (according to twitter) to extract e.g. [{"user":"******"}, text'] is default Time is automatically added in to database Temporary = True, Bool - Store Tweets in the Database temporarily or permanently Default = True """ exit_code = subprocess.check_call(["mysql", "server", "start"]) if exit_code == 0: pass else: raise Warning( "Mysql server did not start, may want to start server manually") time.sleep(5) auth = tweepy.OAuthHandler(consumer_key=C_Key, consumer_secret=C_Secret) auth.set_access_token(A_Token, A_Token_Secret) db_connection = MySQLdb.connect("127.0.0.1", "root", "redcard", "tweet_store", charset='utf8mb4') cursor = db_connection.cursor() tweet_add_milestone = int(Max_Tweets / 5) # ## Define a class to listen to the twitter API # If we want to use twitter data and/or a database other than the default then define this custom listener: class Stream_Listener(tweepy.StreamListener): def __init__(self, api=None, Max_Tweets_=None, Table_Name_=None, New_Table_Columns_=None, Tweet_Data_Parts_=None): super().__init__() self.num_tweets = 0 self.max_tweets = Max_Tweets_ self.table_name = Table_Name_ self.tweet_data_parts = Tweet_Data_Parts_ self.new_table_columns = New_Table_Columns_ # For creating, create table if not default # Below line is hide your warning cursor.execute("SET sql_notes = 0; ") # create table here.... exec_stmt = str( "CREATE TABLE IF NOT EXISTS " + self.table_name + " (date DATETIME, username VARCHAR(20), tweet VARCHAR(280)) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;" ) cursor.execute(exec_stmt) db_connection.commit() def on_data(self, data): if self.num_tweets < self.max_tweets: all_data = json.loads(data) tweet = all_data["text"] username = all_data["user"]["screen_name"] cur_time = datetime.datetime.strptime( all_data["created_at"], "%a %b %d %H:%M:%S %z %Y") exec_stmt = str("INSERT INTO " + self.table_name + " (date, username, tweet) VALUES (%s, %s, %s)") cursor.execute(exec_stmt, (cur_time, username, tweet)) db_connection.commit() if self.num_tweets % tweet_add_milestone == 0 or self.num_tweets == 0: print("Successfully added tweet. Number:", self.num_tweets + 1) self.num_tweets += 1 return True else: print("Finished writing to table:", self.table_name) return False def on_error(self, status): print("Error Code:", status) #Initialise the stream listener listener = Stream_Listener(Max_Tweets_=Max_Tweets, Table_Name_=Table_Name, New_Table_Columns_=New_Table_Columns, Tweet_Data_Parts_=Tweet_Data_Parts) #Authenticate the listener data_stream = tweepy.Stream(auth, listener) #Add filters data_stream.filter(track=Filters) subprocess.check_call(["mysql.server", "stop"]) print("Database Server Successfully written to and MySQL server stopped") # First create the engine to connect to the database engine = sqlalchemy.create_engine( 'mysql+mysqldb://root:[email protected]/tweet_store') #Set up a metadata object to track table metadata meta_data = sqlalchemy.MetaData() tweet_table = sqlalchemy.Table(Table_Name, meta_data, autoload=True, autoload_with=engine) #Establish the database connection connection = engine.connect() return db_connection
from app.core.config import DATABASE_URL_STR import sqlalchemy engine = sqlalchemy.create_engine(DATABASE_URL_STR) connection = engine.connect() metadata = sqlalchemy.MetaData() objects = sqlalchemy.Table( "objects", metadata, sqlalchemy.Column("id", sqlalchemy.String), sqlalchemy.Column("name", sqlalchemy.String), sqlalchemy.Column("description", sqlalchemy.String), sqlalchemy.Column("self_uri", sqlalchemy.String), sqlalchemy.Column("size", sqlalchemy.BigInteger), sqlalchemy.Column("created_time", sqlalchemy.DateTime), sqlalchemy.Column("updated_time", sqlalchemy.DateTime), sqlalchemy.Column("version", sqlalchemy.String), sqlalchemy.Column("mime_type", sqlalchemy.String), sqlalchemy.Column("aliases", sqlalchemy.String), ) checksums = sqlalchemy.Table( "checksums", metadata, sqlalchemy.Column("object_id", sqlalchemy.String), sqlalchemy.Column("type", sqlalchemy.String), sqlalchemy.Column("checksum", sqlalchemy.String), )
wss += calculate_wss(df_limit) return wss # Load the data from database # Build database connection engine = db.create_engine('mssql+pyodbc://adminuser:Yxcvbnm@[email protected]/ProMi?driver=ODBC+Driver+17+for+SQL+Server') con = engine.connect() metadata = db.MetaData(schema = 'PROM') # Get event log from database table = db.Table('Event_Log_Compliant',metadata,autoload = True, autoload_with=engine) ResultProxy= con.execute(db.select([table])) ResultSet = ResultProxy.fetchall() df_export = pd.DataFrame(ResultSet) df_export.columns = ResultSet[0].keys() #df_export = pd.read_csv('C:/Users/DEVMEYE3/Documents/Master/BPI Challenge 2019/Exports/CLUSTER_02_02_compliant_INV_before_GR_without_SRM.txt', sep = ',', encoding = 'utf-8') df_total = df_export.filter(items = ['_case_concept_name_', '_event_concept_name_']) # Calculate total within-sum-of-squares #wss_total = calculate_wss(df_total)
def _table_from_schema(self, name, schema, database: str | None = None) -> sqlalchemy.Table: columns = self._columns_from_schema(name, schema) return sqlalchemy.Table(name, self.meta, schema=database, *columns)
from bs4 import BeautifulSoup from sqlalchemy.dialects.postgresql import insert from celery import Celery, Task from datetime import datetime from sqlalchemy.orm import sessionmaker, scoped_session from requests.exceptions import RequestException # Init SQLAlchemy db_engine = sa.create_engine( 'postgres://postgres@pgbouncer:6432/celery-worker-demo', pool_recycle=3600, pool_size=10) db_metadata = sa.MetaData() table_domains = sa.Table('domains', db_metadata, autoload=True, autoload_with=db_engine) table_result = sa.Table('result', db_metadata, autoload=True, autoload_with=db_engine) def db_new_session(): return scoped_session( sessionmaker(autocommit=False, autoflush=False, bind=db_engine)) # Init Celery # ./app/entrypoint.sh to see the starting command
# revision identifiers, used by Alembic. revision = 'whatever-rev' down_revision = 'whatever-down-rev' from alembic import op import sqlalchemy as sa import uuid from yourmodel import somemodel standardattrs = sa.Table( 'standardattributes', sa.MetaData(), sa.Column('id', sa.BigInteger(), primary_key=True, autoincrement=True), sa.Column('resource_type', sa.String(length=255), nullable=False), sa.Column('created_at', sa.DateTime(), nullable=False), sa.Column('updated_at', sa.DateTime(), nullable=False)) def get_values(): session = sa.orm.Session(bind=op.get_bind()) values = [] with session.begin(subtransactions=True): for row in session.query(somemodel.YourOrmClaas).all(): created_at = rec.standard_attr.created_at updated_at = rec.standard_attr.updated_at std_attr = session.execute( # NOTE **{} is a little trick to pass pylink test # it doesn't impact migraion job. Ref: # - https://github.com/PyCQA/pylint/issues/722
import datetime import sqlalchemy from flask_login import UserMixin from sqlalchemy import orm from sqlalchemy_serializer import SerializerMixin from .db_session import SqlAlchemyBase association_table = sqlalchemy.Table( 'association', SqlAlchemyBase.metadata, sqlalchemy.Column('jobs', sqlalchemy.Integer, sqlalchemy.ForeignKey('jobs.id')), sqlalchemy.Column('category', sqlalchemy.Integer, sqlalchemy.ForeignKey('category.id'))) class Category(SqlAlchemyBase, UserMixin, SerializerMixin): __tablename__ = 'category' id = sqlalchemy.Column(sqlalchemy.Integer, primary_key=True, autoincrement=True) name = sqlalchemy.Column(sqlalchemy.String, nullable=True) class Jobs(SqlAlchemyBase, UserMixin, SerializerMixin): __tablename__ = 'jobs' id = sqlalchemy.Column(sqlalchemy.Integer, primary_key=True, autoincrement=True) # team_leader = sqlalchemy.Column(sqlalchemy.Integer, nullable=True)
def get_metadef_properties_table(meta): return sqlalchemy.Table('metadef_properties', meta, autoload=True)
class TransitionDb(): transition = sa.Table( 'transition', meta, sa.Column('id', sa.Integer, primary_key=True, nullable=False), sa.Column('name', sa.String(80), nullable=False, unique=True), sa.Column('strategy_name', sa.String(120), unique=False), sa.Column('object', sa.String(120), unique=False), sa.Column('duration', sa.Integer, unique=False), sa.Column('customize_parameter', sa.String(1024), unique=False)) @classmethod async def queryDbByName(cls, conn, name): result = await conn.execute( cls.transition.select().where(cls.transition.c.name == name)) transition = await result.first() if transition: return cls(transition.name, transition.strategy_name, transition.object, transition.customize_parameter, transition.id) else: return None @classmethod async def queryDbById(cls, conn, id): result = await conn.execute( cls.transition.select().where(cls.transition.c.id == id)) transition = await result.first() return cls(transition.name, transition.strategy_name, transition.object, transition.customize_parameter, transition.id) @classmethod async def getAll(cls, conn): result = await conn.execute(cls.transition.select()) records = await result.fetchall() transitions = [] for transition in records: transitions.append( cls(transition.name, transition.strategy_name, transition.object, transition.customize_parameter, transition.id)) return transitions def __init__(self, name, strategyName, object, parameters, id=''): self.name = name self.strategyName = strategyName self.object = object self.duration = 100 self.customizeParameter = parameters self.id = id def toJSON(self): return json.dumps(self.toDict()) def toDict(self): return { "id": self.id, "name": self.name, "strategyName": self.strategyName, "object": self.object, "duration": self.duration, "customizeParameter": self.customizeParameter } async def insertDb(self, conn): id = await conn.scalar(TransitionDb.transition.insert().values( name=self.name, strategy_name=self.strategyName, object=self.object, duration=self.duration, customize_parameter=self.customizeParameter)) if not id: print("Insert Transition id:{id} failed.".format(id=id)) else: self.id = id async def updateDb(self, conn): result = await conn.execute(TransitionDb.transition.update().returning( *TransitionDb.transition.c).where( TransitionDb.transition.c.id == self.id).values( id=self.id, name=self.name, strategy_name=self.strategyName, object=self.object, duration=self.duration, customize_parameter=self.customizeParameter)) record = await result.fetchone() if not record: print("Update Transition id:{id} failed.".format(id=self.id)) def updateFromDict(self, dict): self.name = dict['name'] # strategy changed, the customize parameter maybe changed too # update customize parameter later by quaring isParameterDirty() if (self.strategyName == dict['strategyName']): self.customizeParameter = dict['customizeParameter'] else: self.customizeParameter = '' self.strategyName = dict['strategyName'] self.object = dict['object'] self.duration = dict['duration'] def isParametersEmpty(self): return self.customizeParameter == '' def setCustomizeParameter(self, parameters): self.customizeParameter = parameters def __repr__(self): return '<User %r>' % self.name
def upload(request): #context ={} if request.method == 'POST': upload_file = request.FILES['document'] engine = db.create_engine( 'mysql://*****:*****@210.179.174.148:3306/enc_bigdata?charset=utf8', convert_unicode=False) connection = engine.raw_connection() metadata = db.MetaData() print(upload_file.name) if '스케쳐스출고' in upload_file.name: #엑셀 mysql 업로드하기 df = pd.read_excel( io='C:/Users/Public/Documents/' + upload_file.name, sheet_name='Sheet1', engine='openpyxl', converters={ 'owr_cd': str, 'post_no': str, 'postno2': str, 'etc_mtter2': str }, usecols=[ 'dlivr_decsn_oprto_cd', 'dlivr_no', 'delvr_cd', 'dlivr_prgrs_stts_nm', 'btch_prces_no', 'sppm_key_no', 'dlivr_tpe_sctin_nm', 'dlivr_stts_sctin_nm', 'order_dtm', 'rcrt_phsph_nm', 'dlivr_prrrg_dtm', 'dlivr_cmple_dtm', 'dlivr_orer', 'owr_cd', 'prlst_cd', 'dlivr_prrrg_qntt', 'dlivr_cmple_qntt', 'bscnn_cd', 'owr_nm', 'order_tpe_sctin_nm', 'delvr_prrrg_dtm', 'delvr_dtm', 'delvr_prrt', 'wrhs_cd', 'dt_acto_stre_dlivr_no', 'dlivr_wrhs_nm', 'shpmn_dirct_no', 'bscnn_nm', 'dlgds_offce_cd', 'dlgds_offce_nm', 'dlgds_offce_bss_addr', 'rcrt_phsph_dtadd', 'post_no', 'postno2', 'pinch_mtlno', 'pinch_email', 'bscnn_rfrnc_nm', 'assgn_group_nm', 'rmrk', 'etc_mtter1', 'etc_mtter2', 'mvmnt_key_no', 'trnso_headr_no', 'dlivr_order_crtin_dtm', 'dlivr_rgter_id1', 'dlivr_updt_dtm', 'dlivr_upusr_cd', 'dlivr_oprto_pc_cd1', 'order_cstmr_nm', 'order_cstmr_tlno', 'order_cstmr_mtlno', 'rcrt_phsph_tlno', 'clssc_key_no', 'clssc_lne_no', 'order_key_no', 'order_lne_no', 'prlst_group_cd', 'prlst_brcd_no', 'lt_attrb_nm1', 'lt_attrb_nm2', 'lt_attrb_nm3', 'lt_attrb_nm4', 'lt_attrb_nm5', 'un_ldng_sctin_val', 'etc_sctin_val', 'crsdk_qntt', 'assgn_qntt', 'pckn_qntt', 'clssc_qntt', 'dlivr_cncll_qntt', 'sal_unprc_amt', 'sal_amt', 'vat', 'goods_rmrk', 'invnr_lck_cont', 'lt_rsrvt_sctin_cont', 'crsdk_sctin_val', 'mvmnt_lne_no', 'sppm_lne_no', 'dlivr_rgstn_dtm', 'dlivr_rgter_id2', 'dlivr_prces_dtm', 'dlivr_oprto_pc_cd2', 'spml_prlst_no', 'ordno1', 'ordno2', 'rcrt_phsph_fxno', 'decsn_qntt', 'prlst_nm', 'dlivr_decsn_dtm', 'dlivr_prces_stts_cd', 'ctdl_delay_dcnt', 'bscnn_dlivr_rcipt_dtm', 'cmptn_dlivr_prces_delay_dcnt' ]) table = db.Table('tb_en_skx_dlivr_stats_s_02', metadata, autoload=True, autoload_with=engine) df.to_sql('tb_en_skx_dlivr_stats_s_02', con=engine, if_exists='append', index=False) # print('프로시저 시작') # cursor = connection.cursor() # print('프로시저 중간') # cursor.callproc("tb_en_skx_dlivr_pro") # results = list(cursor.fetchall()) # connection.commit() # print('프로시저 끝') # cursor.close() elif '스케쳐스입고' in upload_file.name: #엑셀 mysql 업로드하기 df = pd.read_excel( io='C:/Users/Public/Documents/' + upload_file.name, sheet_name='Sheet1', engine='openpyxl', converters={ 'bscnn_cd': str, 'cmpn_cd': str, 'owr_cd': str }, usecols=[ 'wrhsn_no', 'bscnn_cd', 'bscnn_nm', 'wrhsn_type_cd', 'prlst_brcd_no', 'wrhsn_stts_sctin_nm', 'wrhsn_prrrg_dtm', 'wrhsn_cmple_dtm', 'wrhs_cd', 'lt_attrb_nm1', 'prrrg_wrhsn_qntt', 'rtwr_qntt', 'wrhsn_wrhs_nm', 'bscnn_bss_addr', 'wrhsn_dirct_no', 'bscnn_dtadd', 'rmrk', 'cmpn_cd', 'cmpn_nm', 'wrhsn_prlst_no', 'prlst_group_cd', 'prcrc_dtm', 'prlst_cd', 'owr_cd', 'prlst_nm', 'prlst_rmrk', 'wrhsn_prces_stts_cd', 'owr_nm', 'bscnn_wrhsn_rcipt_dtm', 'wrhsn_dirct_pinch_nm' ]) table = db.Table('tb_en_skx_wrhsn_stats_s_02', metadata, autoload=True, autoload_with=engine) df.to_sql('tb_en_skx_wrhsn_stats_s_02', con=engine, if_exists='append', index=False) # print('프로시저 시작') # cursor = connection.cursor() # print('프로시저 중간') # cursor.callproc("tb_en_skx_wrhsn_pro") # results = list(cursor.fetchall()) # connection.commit() # print('프로시저 끝') # cursor.close() elif '스케쳐스반품' in upload_file.name: #엑셀 mysql 업로드하기 df = pd.read_excel(io='C:/Users/Public/Documents/' + upload_file.name, sheet_name='Sheet1', engine='openpyxl', converters={ 'owr_cd': str, 'cmpn_cd': str, 'companycd1': str }) table = db.Table('tb_en_skx_rtgds_stats_s_02', metadata, autoload=True, autoload_with=engine) df.to_sql('tb_en_skx_rtgds_stats_s_02', con=engine, if_exists='append', index=False) print('프로시저 시작') cursor = connection.cursor() print('프로시저 중간') cursor.callproc("tb_en_skx_rtgds_pro") results = list(cursor.fetchall()) connection.commit() print('프로시저 끝') cursor.close() elif '쏨니아출고' in upload_file.name: #엑셀 mysql 업로드하기 # df = pd.read_excel(io='C:/Users/Public/Documents/'+upload_file.name,sheet_name='Sheet1', engine='openpyxl',converters={'BSCNN_CD':str, 'OWR_CD':str, 'POST_NO':str}) df = pd.read_excel(io='C:/Users/Public/Documents/' + upload_file.name, sheet_name='Sheet1', engine='openpyxl', converters={ 'bscnn_cd': str, 'owr_cd': str, 'post_no': str }) table = db.Table('tb_en_som_dlivr_stats_s_02', metadata, autoload=True, autoload_with=engine) df.to_sql('tb_en_som_dlivr_stats_s_02', con=engine, if_exists='append', index=False) # print('프로시저 시작') # cursor = connection.cursor() # print('프로시저 중간') # cursor.callproc("tb_en_som_dlivr_pro") # results = list(cursor.fetchall()) # connection.commit() # print('프로시저 끝') # cursor.close() elif '쏨니아입고' in upload_file.name: #엑셀 mysql 업로드하기 # df = pd.read_excel(io='C:/Users/Public/Documents/'+upload_file.name,sheet_name='Sheet1', engine='openpyxl',converters={'BSCNN_CD':str, 'CMPN_CD':str, 'OWR_CD':str}) df = pd.read_excel(io='C:/Users/Public/Documents/' + upload_file.name, sheet_name='Sheet1', engine='openpyxl', converters={ 'bscnn_cd': str, 'cmpn_cd': str, 'owr_cd': str }) table = db.Table('tb_en_som_wrhsn_stats_s_02', metadata, autoload=True, autoload_with=engine) df.to_sql('tb_en_som_wrhsn_stats_s_02', con=engine, if_exists='append', index=False) # print('프로시저 시작') # cursor = connection.cursor() # print('프로시저 중간') # cursor.callproc("tb_en_som_wrhsn_pro") # results = list(cursor.fetchall()) # connection.commit() # print('프로시저 끝') # cursor.close() elif '쏨니아반품' in upload_file.name: #엑셀 mysql 업로드하기 # df = pd.read_excel(io='C:/Users/Public/Documents/'+upload_file.name,sheet_name='Sheet1', engine='openpyxl',converters={'BSCNN_CD':str, 'CMPN_CD':str, 'OWR_CD':str}) df = pd.read_excel(io='C:/Users/Public/Documents/' + upload_file.name, sheet_name='Sheet1', engine='openpyxl', converters={ 'bscnn_cd': str, 'cmpn_cd': str, 'owr_cdOWR_CD': str }) table = db.Table('tb_en_som_rtgds_stats_s_02', metadata, autoload=True, autoload_with=engine) df.to_sql('tb_en_som_rtgds_stats_s_02', con=engine, if_exists='append', index=False) #print('프로시저 시작') #cursor = connection.cursor() #print('프로시저 중간') #cursor.callproc("tb_en_som_rtgds_pro") #results = list(cursor.fetchall()) #connection.commit() #print('프로시저 끝') #cursor.close() elif '공동물류출고' in upload_file.name: #엑셀 mysql 업로드하기 df = pd.read_excel(io='C:/Users/Public/Documents/' + upload_file.name, sheet_name='Sheet1', engine='openpyxl') table = db.Table('tb_en_com_dlivr_stats_s_02', metadata, autoload=True, autoload_with=engine) df.to_sql('tb_en_com_dlivr_stats_s_02', con=engine, if_exists='append', index=False) # print('프로시저 시작') # cursor = connection.cursor() # print('프로시저 중간') # cursor.callproc("tb_en_com_dlivr_pro") # results = list(cursor.fetchall()) # connection.commit() # print('프로시저 끝') # cursor.close() elif '공동물류입고' in upload_file.name: #엑셀 mysql 업로드하기 # df = pd.read_excel(io='C:/Users/Public/Documents/'+upload_file.name,sheet_name='Sheet1', engine='openpyxl' # ,usecols=[ # 'OWR_CMPN_NM','IN_DIRCT_NO_1','PRCRC_NO','WRHSN_DT_1','WRHSN_TPE','WRHSN_ORER','SUPPLY_MEN_CD','ROW_NO','WRHSN_PRRRG_DT','WRHSN_DECSN_DT' # ,'STTS','DTLS_STTS','ERR_MES_1','RMRK_1','RGTER_1','UPUSR_1','RGSTN_DT_1','UPDT_DT_1','UPL_ORER_1','IN_DIRCT_NO_2' # ,'GOODS_CD_1','MNFT_DT','EXPDATE','WAIT_ZNE','WRHSN_DT_2','PRRRG_QNTT','ACCPN_QNTT','CAU','GOODS_STTS','IN_LOT' # ,'ACCPN_STTS','ACCPN_DECSN_DT','ADJT_STTS','RMRK_2','ERR_MES_2','RGTER_2','UPUSR_2','RGSTN_DT_2','UPDT_DT_2','UPL_ORER_2' # ,'TPE','GOODS_CD_2','GOODS_NM','USE_WHTHR','BRCD_1','BRCD_2','BRCD_3','HNDLE_CTON','UNIT_CD','UNIT_NM' # ,'SPLR','SET_GOODS_WHTHR','ACQRE_SCTIN','WRHSN_UNPRC','DLIVR_UNPRC','VAT_TPE','LRGE_CLSS','MDUM_CLSS','SML_CLSS','GOODS_BRND' # ,'GOODS_LNE','PROKND','GNDR','CTGRY','PRDCT_YR','PRDCT_SSN','PRONMBR','SIZE','COLOR','GOODS_SCTIN' # ,'STRG_TPE','STNDRD','BX_PER_QNTT','PLLT_PER_QNTT','PLLT_PER_BX_QNTT','WDTH','LNGTH','HGHT','WGHT','BX_CBM' # ,'THR_SDE_SUM','PIECE_PER_CBM','INVNR_MNGMNT','RCPTNDSBR_MNGMNT','LOT_MNGMNT','DSTRBTN_PRCSS_WHETHER','ASSGNMNT_WHETHER','EXPIRYDATE_MNGMNT_WHETHER','CNSLDTN_DSLLWNCE_WHETHER','UNIT_DLIVR' # ,'RMRK_3','RGSTN_DT_3','RGTER_3','UPDT_DT_3','UPUSR_3' # ]) df = pd.read_excel( io='C:/Users/Public/Documents/' + upload_file.name, sheet_name='Sheet1', engine='openpyxl', usecols=[ 'owr_cmpn_nm', 'in_dirct_no1', 'prcrc_no', 'wrhsn_dt1', 'wrhsn_tpe', 'wrhsn_orer', 'supply_men_cd', 'row_no', 'wrhsn_prrrg_dt', 'wrhsn_decsn_dt', 'stts', 'dtls_stts', 'err_mes1', 'rmrk1', 'rgter1', 'upusr1', 'rgstn_dt1', 'updt_dt1', 'upl_orer1', 'in_dirct_no2', 'goods_cd1', 'mnft_dt', 'expdate', 'wait_zne', 'wrhsn_dt2', 'prrrg_qntt', 'accpn_qntt', 'cau', 'goods_stts', 'in_lot', 'accpn_stts', 'accpn_decsn_dt', 'adjt_stts', 'rmrk2', 'err_mes2', 'rgter2', 'upusr2', 'rgstn_dt2', 'updt_dt2', 'upl_orer2', 'tpe', 'goods_cd2', 'goods_nm', 'use_whthr', 'brcd_1', 'brcd_2', 'brcd_3', 'hndle_cton', 'unit_cd', 'unit_nm', 'splr', 'set_goods_whthr', 'acqre_sctin', 'wrhsn_unprc', 'dlivr_unprc', 'vat_tpe', 'lrge_clss', 'mdum_clss', 'sml_clss', 'goods_brnd', 'goods_lne', 'proknd', 'gndr', 'ctgry', 'prdct_yr', 'prdct_ssn', 'pronmbr', 'size', 'color', 'goods_sctin', 'strg_tpe', 'stndrd', 'bx_per_qntt', 'pllt_per_qntt', 'pllt_per_bx_qntt', 'wdth', 'lngth', 'hght', 'wght', 'bx_cbm', 'thr_sde_sum', 'piece_per_cbm', 'invnr_mngmnt', 'rcptndsbr_mngmnt', 'lot_mngmnt', 'dstrbtn_prcss_whether', 'assgnmnt_whether', 'expirydate_mngmnt_whether', 'cnsldtn_dsllwnce_whether', 'unit_dlivr', 'rmrk3', 'rgstn_dt3', 'rgter3', 'updt_dt3', 'upusr3' ]) table = db.Table('tb_en_com_wrhsn_stats_s_02', metadata, autoload=True, autoload_with=engine) df.to_sql('tb_en_com_wrhsn_stats_s_02', con=engine, if_exists='append', index=False) # print('프로시저 시작') # cursor = connection.cursor() # print('프로시저 중간') # cursor.callproc("tb_en_com_wrhsn_pro") # results = list(cursor.fetchall()) # connection.commit() # print('프로시저 끝') # cursor.close() elif '공동물류반품' in upload_file.name: #엑셀 mysql 업로드하기 # df = pd.read_excel(io='C:/Users/Public/Documents/'+upload_file.name,sheet_name='Sheet1', engine='openpyxl' # ,usecols=[ # 'OWR_CMPN_NM','IN_DIRCT_NO_1','INVCE_NO','RTGDS_WRHSN_DT','RTGDS_DECSN_DT','RTGDS_TPE','RTGDS_PLC','RTGDS_MEN_NM','ORER_NO','CURR' # ,'ADDR','RTGDS_COST_SCTIN','CAU','TLNO','RMRK_1','RTGDS_STTS','RGTER_1','RGSTN_DT_1','UPUSR_1','UPDT_DT_1' # ,'ORGNL_INVCE_NO','CUST_USE_NO','IN_DIRCT_NO_2','GOODS_CD_1','RTGDS_QNTT','NRWL_QNTT','FULTY_QNTT','MNFT_DT','DSTRBTN_DT','IN_LOT' # ,'RGTER_2','RGSTN_DT_2','UPUSR_2','UPDT_DT_2','GOODS_CD_2','GOODS_NM','USE_WHTHR','BRCD_1','BRCD_2','BRCD_3' # ,'HNDLE_CTON','UNIT_CD','UNIT_NM','SPLR','SET_GOODS_WHTHR','ACQRE_SCTIN','WRHSN_UNPRC','DLIVR_UNPRC','VAT_TPE','LRGE_CLSS' # ,'MDUM_CLSS','SML_CLSS','GOODS_BRND','GOODS_LNE','PROKND','GNDR','CTGRY','PRDCT_YR','PRDCT_SSN','PRONMBR' # ,'SIZE','COLOR','GOODS_SCTIN','STRG_TPE','STNDRD','BX_PER_QNTT','PLLT_PER_QNTT','PLLT_PER_BX_QNTT','WDTH','LNGTH' # ,'HGHT','WGHT','BX_CBM','THR_SDE_SUM','PIECE_PER_CBM','INVNR_MNGMNT','RCPTNDSBR_MNGMNT','LOT_MNGMNT','DSTRBTN_PRCSS_WHETHER','ASSGNMNT_WHTHR' # ,'EXPIRYDATE_MNGMNT_WHETHER','CNSLDTN_DSLLWNCE_WHETHER','UNIT_DLIVR','RMRK','RGSTN_DT_3','RGTER_3','UPDT_DT_3','UPUSR_3' # ]) df = pd.read_excel( io='C:/Users/Public/Documents/' + upload_file.name, sheet_name='Sheet1', engine='openpyxl', usecols=[ 'owr_cmpn_nm', 'in_dirct_no1', 'invce_no', 'rtgds_wrhsn_dt', 'rtgds_decsn_dt', 'rtgds_tpe', 'rtgds_plc', 'rtgds_men_nm', 'orer_no', 'curr', 'addr', 'rtgds_cost_sctin', 'cau', 'tlno', 'rmrk1', 'rtgds_stts', 'rgter1', 'rgstn_dt1', 'upusr1', 'updt_dt1', 'orgnl_invce_no', 'cust_use_no', 'in_dirct_no2', 'goods_cd1', 'rtgds_qntt', 'nrwl_qntt', 'fulty_qntt', 'mnft_dt', 'dstrbtn_dt', 'in_lot', 'rgter2', 'rgstn_dt2', 'upusr2', 'updt_dt2', 'goods_cd2', 'goods_nm', 'use_whthr', 'brcd_1', 'brcd_2', 'brcd_3', 'hndle_cton', 'unit_cd', 'unit_nm', 'splr', 'set_goods_whthr', 'acqre_sctin', 'wrhsn_unprc', 'dlivr_unprc', 'vat_tpe', 'lrge_clss', 'mdum_clss', 'sml_clss', 'goods_brnd', 'goods_lne', 'proknd', 'gndr', 'ctgry', 'prdct_yr', 'prdct_ssn', 'pronmbr', 'size', 'color', 'goods_sctin', 'strg_tpe', 'stndrd', 'bx_per_qntt', 'pllt_per_qntt', 'pllt_per_bx_qntt', 'wdth', 'lngth', 'hght', 'wght', 'bx_cbm', 'thr_sde_sum', 'piece_per_cbm', 'invnr_mngmnt', 'rcptndsbr_mngmnt', 'lot_mngmnt', 'dstrbtn_prcss_whether', 'assgnmnt_whthr', 'expirydate_mngmnt_whether', 'cnsldtn_dsllwnce_whether', 'unit_dlivr', 'rmrk', 'rgstn_dt3', 'rgter3', 'updt_dt3', 'upusr3' ]) table = db.Table('tb_en_com_rtgds_stats_s_02', metadata, autoload=True, autoload_with=engine) df.to_sql('tb_en_com_rtgds_stats_s_02', con=engine, if_exists='append', index=False) # print('프로시저 시작') # cursor = connection.cursor() # print('프로시저 중간') # cursor.callproc("tb_en_com_rtgds_pro") # results = list(cursor.fetchall()) # connection.commit() # print('프로시저 끝') # cursor.close() elif '마이창고출고' in upload_file.name: #엑셀 mysql 업로드하기 # df = pd.read_excel(io='C:/Users/Public/Documents/'+upload_file.name,sheet_name='Sheet1', engine='openpyxl',converters={'BRCD':str} # ,usecols=['DLIVR_DT','CSTMR_NM','GOODS_NM','MDL_NM','BRCD','QNTT']) df = pd.read_excel( io='C:/Users/Public/Documents/' + upload_file.name, sheet_name='Sheet1', engine='openpyxl', converters={'brcd': str}, usecols=[ 'dlivr_dt', 'cstmr_nm', 'goods_nm', 'mdl_nm', 'brcd', 'qntt' ]) table = db.Table('tb_en_myw_dlivr_stats_s_02', metadata, autoload=True, autoload_with=engine) df.to_sql('tb_en_myw_dlivr_stats_s_02', con=engine, if_exists='append', index=False) # print('프로시저 시작') # cursor = connection.cursor() # print('프로시저 중간') # cursor.callproc("tb_en_myw_dlivr_pro") # results = list(cursor.fetchall()) # connection.commit() # print('프로시저 끝') # cursor.close() elif '마이창고입고' in upload_file.name: #엑셀 mysql 업로드하기 # df = pd.read_excel(io='C:/Users/Public/Documents/'+upload_file.name,sheet_name='Sheet1', engine='openpyxl',converters={'BRCD':str} # ,usecols=['WRHSN_DT','CSTMR_NM','GOODS_NM','MDL_NM','BRCD','WRHSN_QNTT','RMRK']) df = pd.read_excel( io='C:/Users/Public/Documents/' + upload_file.name, sheet_name='Sheet1', engine='openpyxl', converters={'brcd': str}, usecols=[ 'wrhsn_dt', 'cstmr_nm', 'goods_nm', 'mdl_nm', 'brcd', 'wrhsn_qntt', 'rmrk' ]) table = db.Table('tb_en_myw_wrhsn_stats_s_02', metadata, autoload=True, autoload_with=engine) df.to_sql('tb_en_myw_wrhsn_stats_s_02', con=engine, if_exists='append', index=False) # print('프로시저 시작') # cursor = connection.cursor() # print('프로시저 중간') # cursor.callproc("tb_en_myw_wrhsn_pro") # results = list(cursor.fetchall()) # connection.commit() # print('프로시저 끝') # cursor.close() elif '마이창고반품' in upload_file.name: #엑셀 mysql 업로드하기 # df = pd.read_excel(io='C:/Users/Public/Documents/'+upload_file.name,sheet_name='Sheet1', engine='openpyxl' # ,usecols=['PRGRS_STTN','SCTIN','RQST_DT','PCIPT_DT','RCLL_DT','CSTMR_NM','ARRVL_PLC','SNR','PRXSTNC_INVCE','RTGDS_INVCE','GOODS_NM']) df = pd.read_excel( io='C:/Users/Public/Documents/' + upload_file.name, sheet_name='Sheet1', engine='openpyxl', usecols=[ 'prgrs_sttn', 'sctin', 'rqst_dt', 'pcipt_dt', 'rcll_dt', 'cstmr_nm', 'arrvl_plc', 'snr', 'prxstnc_invce', 'rtgds_invce', 'goods_nm' ]) table = db.Table('tb_en_myw_rtgds_stats_s_02', metadata, autoload=True, autoload_with=engine) df.to_sql('tb_en_myw_rtgds_stats_s_02', con=engine, if_exists='append', index=False) # print('프로시저 시작') # cursor = connection.cursor() # print('프로시저 중간') # cursor.callproc("tb_en_myw_rtgds_pro") # results = list(cursor.fetchall()) # connection.commit() # print('프로시저 끝') # cursor.close() elif '월별도급인력사용량' in upload_file.name: #엑셀 mysql 업로드하기 # df = pd.read_excel(io='C:/Users/Public/Documents/'+upload_file.name,sheet_name='Sheet1', engine='openpyxl' # ,usecols=['PRGRS_STTN','SCTIN','RQST_DT','PCIPT_DT','RCLL_DT','CSTMR_NM','ARRVL_PLC','SNR','PRXSTNC_INVCE','RTGDS_INVCE','GOODS_NM']) df = pd.read_excel( io='C:/Users/Public/Documents/' + upload_file.name, sheet_name='Sheet1', engine='openpyxl', usecols=[ 'lgst_cntr_nm', 'otsrc_cmpn_nm', 'sxdst_cd', 'otsrc_dtm', 'otsrc_nofpr_cnt' ]) table = db.Table('tb_en_mnb_otsrc_hmfrc_s_02', metadata, autoload=True, autoload_with=engine) df.to_sql('tb_en_mnb_otsrc_hmfrc_s_02', con=engine, if_exists='append', index=False) # print('프로시저 시작') # cursor = connection.cursor() # print('프로시저 중간') # cursor.callproc("tb_en_mnb_otsrc_hmfrc_pro") # results = list(cursor.fetchall()) # connection.commit() # print('프로시저 끝') # cursor.close() return redirect('/admin/excelbigdata/exceldata/') return render(request, 'upload.html')
def get_type_table(metadata): table = sa.Table(g.db_app_name + '_type', metadata, sa.Column('id', sa.Integer, primary_key=True), sa.Column('name', sa.String, nullable=False)) return table
import sqlalchemy as sa from . import config import sqlalchemy.dialects.postgresql as psql User = sa.Table( 'users', config.metadata, sa.Column('uuid', psql.UUID, primary_key=True, default=config.uuid), sa.Column('username', sa.String(255)), sa.Column('realname', sa.String(255)), sa.Column('email', sa.String(255)), sa.Column('auth_backend', sa.ForeignKey('auth_backends.uuid')), sa.Column('auth_data', sa.JSON), )
def test_table(self): table = sqlalchemy.Table() with fixtures.BannedDBSchemaOperations(['Table']): self.assertRaises(exception.DBNotAllowed, table.drop) self.assertRaises(exception.DBNotAllowed, table.alter)