Clash Royale CLAN TAG#URR8PPP

How to set primary key auto increment in SqlAlchemy orm

I tired to use the SqlAlchemy orm to build the api to insert the values into database from uploaded excel files. when I tested on the codes it kept showing the error:

TypeError: __init__() missing 1 required positional argument: 'id'


I've updated the id key to primary key, auto increment, unique and unsigned in my local MySql data base. I believe the system cannot insert the primary key automatically because it works if I assign the value to id manually

transaction_obj = Transaction(id=1, name="David", date="2018-03-03",
product="fruit", quantity=20, amount=12.55)


Here is model.py

from sqlalchemy import Table, MetaData, Column, Integer, String, DATE, DECIMAL,ForeignKey, DateTime
from sqlalchemy.orm import mapper

metadata = MetaData()


customers = Table('customers', metadata,
Column('id', Integer, primary_key=True),
Column('name', String(20)),
Column('phone', String(20)),
Column('address', String(45)),
Column('source_from', String(45))
)


class Customers(object):
def __init__(self, name, phone, address, source_from):
self.name = name
self.phone = phone
self.address = address
self.source_from = source_from

def __repr__(self):
return "<Customer(name='%s', phone='%s', address='%s', "
"source_from='%s')" % (self.name, self.phone, self.address,
self.source_from)


mapper(Customers, customers)



transaction = Table('transaction', metadata,
Column('id', Integer, primary_key=True),
Column('name', String(20)),
Column('date', DateTime),
Column('product', String(20)),
Column('quantity', Integer),
Column('amount',DECIMAL(2))
)


class Transaction(object):
def __index__(self, name, date, product, quantity, amount):
self.name = name
self.date = date
self.product = product
self.quantity = quantity
self.amount = amount

def __repr__(self):
return "<Transaction(name='%s', date='%s', product='%s',"
"quantity='%s', amount='%s')>" % (self.name, self.date,
self.product, self.quantity,
self.amount)


mapper(Transaction, transaction)


Here is my test coding: test.py

import json
import os
import os
import json
from sqlalchemy import create_engine
import config
import pandas as pd

conn = config.conn_str

def tran_test():
engine = create_engine(conn)
Session_class = sessionmaker(bind=engine)
Session = Session_class
# generate the object for the data we would like to insert

transaction_obj = Transaction(name="David", date="2018-03-03",
product="fruit", quantity=20, amount=12.55)
Session.add(transaction_obj)

Session.commit()

def test_uploaded_file(file):
df = pd.read_excel(file)
return df.info()


if __name__ == '__main__':
# set_env_by_setting('prod')
# conn_str = os.environ.get('ConnectionString')
# print(conn_str)
# test_uploaded_file("-1.xlsx")
tran_test()


I'm using SQLAlchemy==1.2.10, PyMySQL==0.9.2.
I'm doubting if I'm using the wrong format in model.py. Please advise. Thx.

__index__

__init__

1 Answer
1

While I'm not sure about the pattern you are using, (manually mapping to your table classes) I think you would have a much easier time making use of declarative_base which does this for you.

declarative_base

from sqlalchemy.ext.declarative import declarative_base
Base = declarative_base()


Then make sure your models inherit Base

Base

from sqlalchemy import (
Column,
Integer,
String
)


class Customers(Base):
__tablename__ = 'customer'
id = Column(Integer, primary_key=True) # Auto-increment should be default
name = Column(String(20))
# Etc.

def __repr__(self):
return "<Customer(name='%s', phone='%s', address='%s', "
"source_from='%s')" % (self.name, self.phone, self.address,
self.source_from)


And finally use Base to create your table:

Base

Base.metadata.create_all(engine)


Here is a good reference to basic declarative use cases. It gets a little more complicated depending on how you are scaffolding your app but its a great starting point:
http://docs.sqlalchemy.org/en/latest/orm/extensions/declarative/basic_use.html

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.