Mini project - Python

MagicMarker

I started a BSc in Sept, and Python is the language of choice. Personally I don't find the little exercises we get from the lectures particularly interesting so I want to have something to focus on that's a little more productive for me. I want to create an app for work, with a view to replace an existing manual process. Currently when people want to apply a credit to a customer's account they just fill in a excel sheet with the following information...

• Owner - (This is the Sage ledger to be credited/debited, an account could be on more than one ledger)
• Account Number
• Amount (ex vat)
• Reason For Credit/Debit - (Taken from a set list)
• Date
• Name of person

The grand plan is for people to be able to add details through this app, which inserts same into a MS SQL/MySQL database, and to create a facility whereby finance can export that info as and when required to import into Sage.

First things first, I would prefer that the current ledger and the customer's name be retrieved automatically once a user enters an account number. So I'd need to connect to an MS SQL database, however I'm falling at the first hurdle, as google seems to mainly recommend Pyodbc as a means of doing this, but there doesn't seem to be a version compatible with Python 3.4!

So, any pointers or links to tutorials that would cover something like this that I don't know about? Bearing in mind that I've only covered the bare basics in Python thus far.

Overflow

MagicMarker wrote: »

I started a BSc in Sept, and Python is the language of choice. Personally I don't find the little exercises we get from the lectures particularly interesting so I want to have something to focus on that's a little more productive for me. I want to create an app for work, with a view to replace an existing manual process. Currently when people want to apply a credit to a customer's account they just fill in a excel sheet with the following information...

• Owner - (This is the Sage ledger to be credited/debited, an account could be on more than one ledger)
• Account Number
• Amount (ex vat)
• Reason For Credit/Debit - (Taken from a set list)
• Date
• Name of person

The grand plan is for people to be able to add details through this app, which inserts same into a MS SQL/MySQL database, and to create a facility whereby finance can export that info as and when required to import into Sage.

First things first, I would prefer that the current ledger and the customer's name be retrieved automatically once a user enters an account number. So I'd need to connect to an MS SQL database, however I'm falling at the first hurdle, as google seems to mainly recommend Pyodbc as a means of doing this, but there doesn't seem to be a version compatible with Python 3.4!

So, any pointers or links to tutorials that would cover something like this that I don't know about? Bearing in mind that I've only covered the bare basics in Python thus far.

I think you will find that not all libraries support version 3 yet, so just stick with 2.7 for now.

You could use pysqlite, its the easiest way to get a DB up and running for development.

MagicMarker

Ok cool, figured it'd be best to go for the most recent version.

So I installed 2.7 instead and Pyodbc, and now I'm connected to the database and able to run basic queries no bother! Happy days!

Baby steps...

MagicMarker

Only gotten time to come back to this today. I've made up a test database that I can use to insert values, with only 3 tables so far...

The main adjustment table

[dbo].[adjustment](
	[id] [int] IDENTITY(1,1) NOT NULL,
        [company_id] [int] NOT NULL,
	[bs_accref] [nvarchar](10) NOT NULL,
	[reason_id] [int] NOT NULL,
	[amount] [numeric](16, 6) NOT NULL,
	[is_debit] [numeric](1, 0) NOT NULL,
	[user_id] [int] NOT NULL,
	[created_on] [datetime] NOT NULL

Company table.

[dbo].[company](
	[Id] [int] IDENTITY(1,1) NOT NULL,
	[Name] [varchar](20) NULL

Reason Table

[dbo].[reason](
	[Id] [int] IDENTITY(1,1) NOT NULL,
	[Narrative] [nvarchar](150) NULL

And I've concluded with just a basic script that allows me to insert values into the adjustment table based on my inputs..

[PHP]bs_accref = input("Please Enter Account Number: ")
company_id = int(input("Please enter company id: "))
reason_id = int(input("Please enter reason id: "))
amount = float(input("Please enter amount: "))
is_debit = int(input("Is this a debit? 1 for Yes, 0 for No: "))
user_id = int(input("What's your user id: "))

cursor.execute(
"""
INSERT INTO dbo.adjustment (bs_accref, company_id, reason_id, amount, is_debit, user_id, created_on)
VALUES (?, ?, ?, ?, ?, ?, GETDATE())
""", bs_accref, company_id, reason_id, amount, is_debit, user_id)
cnxn.commit()[/PHP]

Obviously, this means I have to write the ids for the reason/company etc. Whereas I want to write in the string narrative for each, and for this to then insert the id value into the adjustment table, though I'm not certain on how to do it. I thought I could create a new reason_str and get the id based on that, like so...

[PHP]reason_str = input("Please enter reason: ")
reason_id = cursor.execute(
"""
SELECT
id
FROM
reason
WHERE
narrative = ?
""", reason_str)[/PHP]


Am I way off base here?

MagicMarker

Ok making some progress now... Getting the required ids from functions, added an 'is_debit' column to the reasons table as whether it is a debit or credit will depend on the reason used.

No doubt there's an easier way of doing this, so if anyone has any pointers I'd love to hear them!

[PHP]bs_accref = input("Enter Account Number: ")
company_str = input("Enter Company Name: ")
reason_str = input("Enter Reason: ")
amount = float(input("Enter Amount: "))
user_id = int(input("Enter User Id: "))


def debit():
cursor.execute("""SELECT is_debit FROM reason WHERE narrative = ?""", reason_str)
is_debit = cursor.fetchone()
return is_debit[0]


def reason():
cursor.execute("""SELECT id FROM reason WHERE narrative = ?""", reason_str)
reason_id = cursor.fetchone()
return reason_id[0]


def company():
cursor.execute("""SELECT id FROM company WHERE name = ?""", company_str)
company_id = cursor.fetchone()
return company_id[0]

cursor.execute(
"""
INSERT INTO dbo.adjustment (bs_accref, company_id, reason_id, amount, is_debit, user_id, created_on)
VALUES (?, ?, ?, ?, ?, ?, GETDATE())
""", bs_accref, company(), reason(), amount, debit(), user_id)
cnxn.commit()[/PHP]

oscarBravo

The problem with using the narrative to look up reasons is that it has to be spelled exactly. A typo will cause the lookup to fail. Similarly with company names. If the queries on those string values fail to return matching records, the insert will fail.

In a GUI application, you'd present the list of reasons in a drop-down list for selection; for companies (depending how many) you might have an auto-complete sort of drop-down list. The challenge is how to translate those functions into a CLI script.

MagicMarker

You're absolutely right, auto complete drop down menus are exactly what i want eventually, but are way beyond my capabilities now. Right now I'm just wanting to get the very basics down, and let it evolve as I gradually acquire the skills/knowledge to add the features I want.

MagicMarker

Speaking of the GUI, what would be the best way to go about developing a browser based GUI? Would HTML/CSS be sufficient? These are going to be covered this semester. But if there's something better I'd try and include that too in my own time.

oscarBravo

The trickiest part about serving Python up to a browser-based GUI is hosting the application on the web server. Have a look at the Flask or Bottle framework; they both include miniature web servers that you can use during development.