How to Create Transaction with Web3.js

Abstract

The demo code provides developers with an overview of how to sign, send, and receive receipt of transactions, and verify the results of their execution. The sample also provides the event monitoring code so that the developer can understand how to listen to an event one or more times.

Getting Started

Understanding The Functions of the Smart Contract

  • Constructor: The constructor function of the smart contract, which is called when the contract is deploying, at the same time it will initialize the number to _initialNumber;
  • increment: The function of incrementing the number by given _value;
  • rest: The function of resetting the number to 0;
  • getNumber: The function of getting the number.

How to run it

  1. Install dependencies: npm install
  2. Copy the configuration file: cp .env.example .env
  3. Edit the configuration file: vim .env, copy your project ID and private key to the .env file.
PRIVATE_KEY=YOUR_PRIVATE_KEY
INFURA_ID=YOUR_PROJECT_ID

4.   Run the index.js file: node index.js

Interpret Source Code

compile.js

You can't use .sol files directly, you need to compile it to binary file firstly.

Load the smart contract file Incrementer.sol into source variable.

// Load contract
const source = fs.readFileSync("Incrementer.sol", "utf8");

Compile the smart contract file

const input = {
  language: "Solidity",
  sources: {
    "Incrementer.sol": {
      content: source,
    },
  },
  settings: {
    outputSelection: {
      "*": {
        "*": ["*"],
      },
    },
  },
};

const tempFile = JSON.parse(solc.compile(JSON.stringify(input)));

| Note: The version of solidity in this task is 0.8.0, different versions may have different compile ways.

Get the Contract Object

const contractFile = tempFile.contracts["Incrementer.sol"]["Incrementer"];

Export contractFile Object

If you want to use the contractFile object in other js files, you need to export it.

module.exports = contractFile;

index.js

1. Load the Incrementer smart contract from compile file

const contractOfIncrementer = require("./compile");

2. Read private key from environment variables

For security’s sake, the private key is not hard-coded, but it can be read as environment variables. When run this task, the dotenv plugin will automatically read the configurations in the .env file and load them as environment variables, and then you can use the private key and other environment variables via process.env.

require("dotenv").config();
const privatekey = process.env.PRIVATE_KEY;

3. Create the web3 instance

web3 is the main API of the web3js library. It is used to interact with the blockchain.

// Provider
const providerRPC = {
  development: "https://kovan.infura.io/v3/" + process.env.INFURA_ID,
  moonbase: "https://rpc.testnet.moonbeam.network",
};
const web3 = new Web3(providerRPC.development); //Change to correct network

| Note: The INFURA_ID is the PROJECT ID of the Infura project you created in last task

4. Get the account address

On blockchain, each user has a address, which is unique for others, and you can get the address by the private key. In this task, you can use the we3.eth.accounts.privateKeyToAccount API to get your account address by passing the private key as a parameter.

const account = web3.eth.accounts.privateKeyToAccount(privatekey);
const account_from = {
  privateKey: privatekey,
  accountAddress: account.address,
};

5. Get the bytecode and abi

When deploying the smart contract, you need to specify the bytecode and abi of the smart contract. The bytecode is the compiled binary code of the smart contract, and the abi (Application Binary Interface) is the interface of the smart contract.

const bytecode = contractOfIncrementer.evm.bytecode.object;
const abi = contractOfIncrementer.abi;

6. Get contract instance

In the last step, you got the bytecode and abi, so you can create the contract instance by the abi.

// Create contract instance
  const deployContract = new web3.eth.Contract(abi);

7. Create the transaction of the deployContract

// Create Tx
const deployTx = deployContract.deploy({
    data: bytecode,
    arguments: [5],
});

8. Sign the transaction

Use your private key to sign the transaction.

// Sign Tx
const deployTransaction = await web3.eth.accounts.signTransaction(
    {
        data: deployTx.encodeABI(),
        gas: 8000000,
    },
    account_from.privateKey
);

9. Send the transaction / Deploy your smart contract

Send your deploy transaction to the blockchain. You will receive a receipt, and get this contract address from the receipt.

const deployReceipt = await web3.eth.sendSignedTransaction(
    deployTransaction.rawTransaction
);
console.log(`Contract deployed at address: ${deployReceipt.contractAddress}`);

10. Load the contract instance from blockchain through above address

In previous steps, you built a contract instance, and then deployed the transaction by sending the contract to the blockchain so that you can operate the transaction later. Besides, you could also load a contract instance that is already on the blockchain so that you can operate it directly and avoid the process of deploying it.

let incrementer = new web3.eth.Contract(abi, deployReceipt.contractAddress);

11. Use the view function of a contract

Whether a contract instance is create by deploying, or by loading, you can interact with the contract once you have an instance of the contract already on the blockchain.
There are two types of contract functions: view and without view. The functions with view promise not to modify the state, while the functions without view will generate the corresponding block data on the blockchain. For example, after calling the getNumber function of the contract, you will get the public variable number of the contract, and this operation will not charge any gas.

let number = await incrementer.methods.getNumber().call();

12. Build a transaction

Before you send the transaction to the blockchain, you need to build the transaction, it means you need to specify the parameters of the transaction.

let incrementTx = incrementer.methods.increment(_value);

// Sign with Pk
let incrementTransaction = await web3.eth.accounts.signTransaction(
  {
    to: createReceipt.contractAddress,
    data: incrementTx.encodeABI(),
    gas: 8000000,
  },
  account_from.privateKey
);

13. Send the transaction and get the receipt

You can use sendSignedTransaction function to send above transaction to the blockchain, and got the receipt to check result.

const incrementReceipt = await web3.eth.sendSignedTransaction(
  incrementTransaction.rawTransaction
);

Listen to Event Increment

When invoking the interfaces of the contract, the only way to get information about the processing details, apart from the results returned by the interface, is through events.
In the interfaces, you retrieve the corresponding internal information by triggering an event, and then capturing this event generated by the external block.

const web3Socket = new Web3(
new Web3.providers.WebsocketProvider(
    'wss://kovan.infura.io/ws/v3/' + process.env.INFURA_ID
));
incrementer = new web3Socket.eth.Contract(abi, createReceipt.contractAddress);

| kovan don't support http protocol to event listen, need to use websocket. More details , please refer to this blog

Listen to Increment event only once

incrementer.once('Increment', (error, event) => {
    console.log('I am a onetime event listener, I am going to die now');
});

Listen to Increment event continuously

incrementer.events.Increment(() => {
    console.log("I am a longlive event listener, I get a event now");
});

References

Link: https://github.com/Dapp-Learning-DAO/Dapp-Learning/tree/main/basic/02-web3js-transaction

#solidity #blockchain #smartcontract #web3 #web3.js

What is GEEK

Buddha Community

How to Create Transaction with Web3.js
Easter  Deckow

Easter Deckow

1655630160

PyTumblr: A Python Tumblr API v2 Client

PyTumblr

Installation

Install via pip:

$ pip install pytumblr

Install from source:

$ git clone https://github.com/tumblr/pytumblr.git
$ cd pytumblr
$ python setup.py install

Usage

Create a client

A pytumblr.TumblrRestClient is the object you'll make all of your calls to the Tumblr API through. Creating one is this easy:

client = pytumblr.TumblrRestClient(
    '<consumer_key>',
    '<consumer_secret>',
    '<oauth_token>',
    '<oauth_secret>',
)

client.info() # Grabs the current user information

Two easy ways to get your credentials to are:

  1. The built-in interactive_console.py tool (if you already have a consumer key & secret)
  2. The Tumblr API console at https://api.tumblr.com/console
  3. Get sample login code at https://api.tumblr.com/console/calls/user/info

Supported Methods

User Methods

client.info() # get information about the authenticating user
client.dashboard() # get the dashboard for the authenticating user
client.likes() # get the likes for the authenticating user
client.following() # get the blogs followed by the authenticating user

client.follow('codingjester.tumblr.com') # follow a blog
client.unfollow('codingjester.tumblr.com') # unfollow a blog

client.like(id, reblogkey) # like a post
client.unlike(id, reblogkey) # unlike a post

Blog Methods

client.blog_info(blogName) # get information about a blog
client.posts(blogName, **params) # get posts for a blog
client.avatar(blogName) # get the avatar for a blog
client.blog_likes(blogName) # get the likes on a blog
client.followers(blogName) # get the followers of a blog
client.blog_following(blogName) # get the publicly exposed blogs that [blogName] follows
client.queue(blogName) # get the queue for a given blog
client.submission(blogName) # get the submissions for a given blog

Post Methods

Creating posts

PyTumblr lets you create all of the various types that Tumblr supports. When using these types there are a few defaults that are able to be used with any post type.

The default supported types are described below.

  • state - a string, the state of the post. Supported types are published, draft, queue, private
  • tags - a list, a list of strings that you want tagged on the post. eg: ["testing", "magic", "1"]
  • tweet - a string, the string of the customized tweet you want. eg: "Man I love my mega awesome post!"
  • date - a string, the customized GMT that you want
  • format - a string, the format that your post is in. Support types are html or markdown
  • slug - a string, the slug for the url of the post you want

We'll show examples throughout of these default examples while showcasing all the specific post types.

Creating a photo post

Creating a photo post supports a bunch of different options plus the described default options * caption - a string, the user supplied caption * link - a string, the "click-through" url for the photo * source - a string, the url for the photo you want to use (use this or the data parameter) * data - a list or string, a list of filepaths or a single file path for multipart file upload

#Creates a photo post using a source URL
client.create_photo(blogName, state="published", tags=["testing", "ok"],
                    source="https://68.media.tumblr.com/b965fbb2e501610a29d80ffb6fb3e1ad/tumblr_n55vdeTse11rn1906o1_500.jpg")

#Creates a photo post using a local filepath
client.create_photo(blogName, state="queue", tags=["testing", "ok"],
                    tweet="Woah this is an incredible sweet post [URL]",
                    data="/Users/johnb/path/to/my/image.jpg")

#Creates a photoset post using several local filepaths
client.create_photo(blogName, state="draft", tags=["jb is cool"], format="markdown",
                    data=["/Users/johnb/path/to/my/image.jpg", "/Users/johnb/Pictures/kittens.jpg"],
                    caption="## Mega sweet kittens")

Creating a text post

Creating a text post supports the same options as default and just a two other parameters * title - a string, the optional title for the post. Supports markdown or html * body - a string, the body of the of the post. Supports markdown or html

#Creating a text post
client.create_text(blogName, state="published", slug="testing-text-posts", title="Testing", body="testing1 2 3 4")

Creating a quote post

Creating a quote post supports the same options as default and two other parameter * quote - a string, the full text of the qote. Supports markdown or html * source - a string, the cited source. HTML supported

#Creating a quote post
client.create_quote(blogName, state="queue", quote="I am the Walrus", source="Ringo")

Creating a link post

  • title - a string, the title of post that you want. Supports HTML entities.
  • url - a string, the url that you want to create a link post for.
  • description - a string, the desciption of the link that you have
#Create a link post
client.create_link(blogName, title="I like to search things, you should too.", url="https://duckduckgo.com",
                   description="Search is pretty cool when a duck does it.")

Creating a chat post

Creating a chat post supports the same options as default and two other parameters * title - a string, the title of the chat post * conversation - a string, the text of the conversation/chat, with diablog labels (no html)

#Create a chat post
chat = """John: Testing can be fun!
Renee: Testing is tedious and so are you.
John: Aw.
"""
client.create_chat(blogName, title="Renee just doesn't understand.", conversation=chat, tags=["renee", "testing"])

Creating an audio post

Creating an audio post allows for all default options and a has 3 other parameters. The only thing to keep in mind while dealing with audio posts is to make sure that you use the external_url parameter or data. You cannot use both at the same time. * caption - a string, the caption for your post * external_url - a string, the url of the site that hosts the audio file * data - a string, the filepath of the audio file you want to upload to Tumblr

#Creating an audio file
client.create_audio(blogName, caption="Rock out.", data="/Users/johnb/Music/my/new/sweet/album.mp3")

#lets use soundcloud!
client.create_audio(blogName, caption="Mega rock out.", external_url="https://soundcloud.com/skrillex/sets/recess")

Creating a video post

Creating a video post allows for all default options and has three other options. Like the other post types, it has some restrictions. You cannot use the embed and data parameters at the same time. * caption - a string, the caption for your post * embed - a string, the HTML embed code for the video * data - a string, the path of the file you want to upload

#Creating an upload from YouTube
client.create_video(blogName, caption="Jon Snow. Mega ridiculous sword.",
                    embed="http://www.youtube.com/watch?v=40pUYLacrj4")

#Creating a video post from local file
client.create_video(blogName, caption="testing", data="/Users/johnb/testing/ok/blah.mov")

Editing a post

Updating a post requires you knowing what type a post you're updating. You'll be able to supply to the post any of the options given above for updates.

client.edit_post(blogName, id=post_id, type="text", title="Updated")
client.edit_post(blogName, id=post_id, type="photo", data="/Users/johnb/mega/awesome.jpg")

Reblogging a Post

Reblogging a post just requires knowing the post id and the reblog key, which is supplied in the JSON of any post object.

client.reblog(blogName, id=125356, reblog_key="reblog_key")

Deleting a post

Deleting just requires that you own the post and have the post id

client.delete_post(blogName, 123456) # Deletes your post :(

A note on tags: When passing tags, as params, please pass them as a list (not a comma-separated string):

client.create_text(blogName, tags=['hello', 'world'], ...)

Getting notes for a post

In order to get the notes for a post, you need to have the post id and the blog that it is on.

data = client.notes(blogName, id='123456')

The results include a timestamp you can use to make future calls.

data = client.notes(blogName, id='123456', before_timestamp=data["_links"]["next"]["query_params"]["before_timestamp"])

Tagged Methods

# get posts with a given tag
client.tagged(tag, **params)

Using the interactive console

This client comes with a nice interactive console to run you through the OAuth process, grab your tokens (and store them for future use).

You'll need pyyaml installed to run it, but then it's just:

$ python interactive-console.py

and away you go! Tokens are stored in ~/.tumblr and are also shared by other Tumblr API clients like the Ruby client.

Running tests

The tests (and coverage reports) are run with nose, like this:

python setup.py test

Author: tumblr
Source Code: https://github.com/tumblr/pytumblr
License: Apache-2.0 license

#python #api 

NBB: Ad-hoc CLJS Scripting on Node.js

Nbb

Not babashka. Node.js babashka!?

Ad-hoc CLJS scripting on Node.js.

Status

Experimental. Please report issues here.

Goals and features

Nbb's main goal is to make it easy to get started with ad hoc CLJS scripting on Node.js.

Additional goals and features are:

  • Fast startup without relying on a custom version of Node.js.
  • Small artifact (current size is around 1.2MB).
  • First class macros.
  • Support building small TUI apps using Reagent.
  • Complement babashka with libraries from the Node.js ecosystem.

Requirements

Nbb requires Node.js v12 or newer.

How does this tool work?

CLJS code is evaluated through SCI, the same interpreter that powers babashka. Because SCI works with advanced compilation, the bundle size, especially when combined with other dependencies, is smaller than what you get with self-hosted CLJS. That makes startup faster. The trade-off is that execution is less performant and that only a subset of CLJS is available (e.g. no deftype, yet).

Usage

Install nbb from NPM:

$ npm install nbb -g

Omit -g for a local install.

Try out an expression:

$ nbb -e '(+ 1 2 3)'
6

And then install some other NPM libraries to use in the script. E.g.:

$ npm install csv-parse shelljs zx

Create a script which uses the NPM libraries:

(ns script
  (:require ["csv-parse/lib/sync$default" :as csv-parse]
            ["fs" :as fs]
            ["path" :as path]
            ["shelljs$default" :as sh]
            ["term-size$default" :as term-size]
            ["zx$default" :as zx]
            ["zx$fs" :as zxfs]
            [nbb.core :refer [*file*]]))

(prn (path/resolve "."))

(prn (term-size))

(println (count (str (fs/readFileSync *file*))))

(prn (sh/ls "."))

(prn (csv-parse "foo,bar"))

(prn (zxfs/existsSync *file*))

(zx/$ #js ["ls"])

Call the script:

$ nbb script.cljs
"/private/tmp/test-script"
#js {:columns 216, :rows 47}
510
#js ["node_modules" "package-lock.json" "package.json" "script.cljs"]
#js [#js ["foo" "bar"]]
true
$ ls
node_modules
package-lock.json
package.json
script.cljs

Macros

Nbb has first class support for macros: you can define them right inside your .cljs file, like you are used to from JVM Clojure. Consider the plet macro to make working with promises more palatable:

(defmacro plet
  [bindings & body]
  (let [binding-pairs (reverse (partition 2 bindings))
        body (cons 'do body)]
    (reduce (fn [body [sym expr]]
              (let [expr (list '.resolve 'js/Promise expr)]
                (list '.then expr (list 'clojure.core/fn (vector sym)
                                        body))))
            body
            binding-pairs)))

Using this macro we can look async code more like sync code. Consider this puppeteer example:

(-> (.launch puppeteer)
      (.then (fn [browser]
               (-> (.newPage browser)
                   (.then (fn [page]
                            (-> (.goto page "https://clojure.org")
                                (.then #(.screenshot page #js{:path "screenshot.png"}))
                                (.catch #(js/console.log %))
                                (.then #(.close browser)))))))))

Using plet this becomes:

(plet [browser (.launch puppeteer)
       page (.newPage browser)
       _ (.goto page "https://clojure.org")
       _ (-> (.screenshot page #js{:path "screenshot.png"})
             (.catch #(js/console.log %)))]
      (.close browser))

See the puppeteer example for the full code.

Since v0.0.36, nbb includes promesa which is a library to deal with promises. The above plet macro is similar to promesa.core/let.

Startup time

$ time nbb -e '(+ 1 2 3)'
6
nbb -e '(+ 1 2 3)'   0.17s  user 0.02s system 109% cpu 0.168 total

The baseline startup time for a script is about 170ms seconds on my laptop. When invoked via npx this adds another 300ms or so, so for faster startup, either use a globally installed nbb or use $(npm bin)/nbb script.cljs to bypass npx.

Dependencies

NPM dependencies

Nbb does not depend on any NPM dependencies. All NPM libraries loaded by a script are resolved relative to that script. When using the Reagent module, React is resolved in the same way as any other NPM library.

Classpath

To load .cljs files from local paths or dependencies, you can use the --classpath argument. The current dir is added to the classpath automatically. So if there is a file foo/bar.cljs relative to your current dir, then you can load it via (:require [foo.bar :as fb]). Note that nbb uses the same naming conventions for namespaces and directories as other Clojure tools: foo-bar in the namespace name becomes foo_bar in the directory name.

To load dependencies from the Clojure ecosystem, you can use the Clojure CLI or babashka to download them and produce a classpath:

$ classpath="$(clojure -A:nbb -Spath -Sdeps '{:aliases {:nbb {:replace-deps {com.github.seancorfield/honeysql {:git/tag "v2.0.0-rc5" :git/sha "01c3a55"}}}}}')"

and then feed it to the --classpath argument:

$ nbb --classpath "$classpath" -e "(require '[honey.sql :as sql]) (sql/format {:select :foo :from :bar :where [:= :baz 2]})"
["SELECT foo FROM bar WHERE baz = ?" 2]

Currently nbb only reads from directories, not jar files, so you are encouraged to use git libs. Support for .jar files will be added later.

Current file

The name of the file that is currently being executed is available via nbb.core/*file* or on the metadata of vars:

(ns foo
  (:require [nbb.core :refer [*file*]]))

(prn *file*) ;; "/private/tmp/foo.cljs"

(defn f [])
(prn (:file (meta #'f))) ;; "/private/tmp/foo.cljs"

Reagent

Nbb includes reagent.core which will be lazily loaded when required. You can use this together with ink to create a TUI application:

$ npm install ink

ink-demo.cljs:

(ns ink-demo
  (:require ["ink" :refer [render Text]]
            [reagent.core :as r]))

(defonce state (r/atom 0))

(doseq [n (range 1 11)]
  (js/setTimeout #(swap! state inc) (* n 500)))

(defn hello []
  [:> Text {:color "green"} "Hello, world! " @state])

(render (r/as-element [hello]))

Promesa

Working with callbacks and promises can become tedious. Since nbb v0.0.36 the promesa.core namespace is included with the let and do! macros. An example:

(ns prom
  (:require [promesa.core :as p]))

(defn sleep [ms]
  (js/Promise.
   (fn [resolve _]
     (js/setTimeout resolve ms))))

(defn do-stuff
  []
  (p/do!
   (println "Doing stuff which takes a while")
   (sleep 1000)
   1))

(p/let [a (do-stuff)
        b (inc a)
        c (do-stuff)
        d (+ b c)]
  (prn d))
$ nbb prom.cljs
Doing stuff which takes a while
Doing stuff which takes a while
3

Also see API docs.

Js-interop

Since nbb v0.0.75 applied-science/js-interop is available:

(ns example
  (:require [applied-science.js-interop :as j]))

(def o (j/lit {:a 1 :b 2 :c {:d 1}}))

(prn (j/select-keys o [:a :b])) ;; #js {:a 1, :b 2}
(prn (j/get-in o [:c :d])) ;; 1

Most of this library is supported in nbb, except the following:

  • destructuring using :syms
  • property access using .-x notation. In nbb, you must use keywords.

See the example of what is currently supported.

Examples

See the examples directory for small examples.

Also check out these projects built with nbb:

API

See API documentation.

Migrating to shadow-cljs

See this gist on how to convert an nbb script or project to shadow-cljs.

Build

Prequisites:

  • babashka >= 0.4.0
  • Clojure CLI >= 1.10.3.933
  • Node.js 16.5.0 (lower version may work, but this is the one I used to build)

To build:

  • Clone and cd into this repo
  • bb release

Run bb tasks for more project-related tasks.

Download Details:
Author: borkdude
Download Link: Download The Source Code
Official Website: https://github.com/borkdude/nbb 
License: EPL-1.0

#node #javascript

Shubham Ankit

Shubham Ankit

1657081614

How to Automate Excel with Python | Python Excel Tutorial (OpenPyXL)

How to Automate Excel with Python

In this article, We will show how we can use python to automate Excel . A useful Python library is Openpyxl which we will learn to do Excel Automation

What is OPENPYXL

Openpyxl is a Python library that is used to read from an Excel file or write to an Excel file. Data scientists use Openpyxl for data analysis, data copying, data mining, drawing charts, styling sheets, adding formulas, and more.

Workbook: A spreadsheet is represented as a workbook in openpyxl. A workbook consists of one or more sheets.

Sheet: A sheet is a single page composed of cells for organizing data.

Cell: The intersection of a row and a column is called a cell. Usually represented by A1, B5, etc.

Row: A row is a horizontal line represented by a number (1,2, etc.).

Column: A column is a vertical line represented by a capital letter (A, B, etc.).

Openpyxl can be installed using the pip command and it is recommended to install it in a virtual environment.

pip install openpyxl

CREATE A NEW WORKBOOK

We start by creating a new spreadsheet, which is called a workbook in Openpyxl. We import the workbook module from Openpyxl and use the function Workbook() which creates a new workbook.

from openpyxl
import Workbook
#creates a new workbook
wb = Workbook()
#Gets the first active worksheet
ws = wb.active
#creating new worksheets by using the create_sheet method

ws1 = wb.create_sheet("sheet1", 0) #inserts at first position
ws2 = wb.create_sheet("sheet2") #inserts at last position
ws3 = wb.create_sheet("sheet3", -1) #inserts at penultimate position

#Renaming the sheet
ws.title = "Example"

#save the workbook
wb.save(filename = "example.xlsx")

READING DATA FROM WORKBOOK

We load the file using the function load_Workbook() which takes the filename as an argument. The file must be saved in the same working directory.

#loading a workbook
wb = openpyxl.load_workbook("example.xlsx")

 

GETTING SHEETS FROM THE LOADED WORKBOOK

 

#getting sheet names
wb.sheetnames
result = ['sheet1', 'Sheet', 'sheet3', 'sheet2']

#getting a particular sheet
sheet1 = wb["sheet2"]

#getting sheet title
sheet1.title
result = 'sheet2'

#Getting the active sheet
sheetactive = wb.active
result = 'sheet1'

 

ACCESSING CELLS AND CELL VALUES

 

#get a cell from the sheet
sheet1["A1"] <
  Cell 'Sheet1'.A1 >

  #get the cell value
ws["A1"].value 'Segment'

#accessing cell using row and column and assigning a value
d = ws.cell(row = 4, column = 2, value = 10)
d.value
10

 

ITERATING THROUGH ROWS AND COLUMNS

 

#looping through each row and column
for x in range(1, 5):
  for y in range(1, 5):
  print(x, y, ws.cell(row = x, column = y)
    .value)

#getting the highest row number
ws.max_row
701

#getting the highest column number
ws.max_column
19

There are two functions for iterating through rows and columns.

Iter_rows() => returns the rows
Iter_cols() => returns the columns {
  min_row = 4, max_row = 5, min_col = 2, max_col = 5
} => This can be used to set the boundaries
for any iteration.

Example:

#iterating rows
for row in ws.iter_rows(min_row = 2, max_col = 3, max_row = 3):
  for cell in row:
  print(cell) <
  Cell 'Sheet1'.A2 >
  <
  Cell 'Sheet1'.B2 >
  <
  Cell 'Sheet1'.C2 >
  <
  Cell 'Sheet1'.A3 >
  <
  Cell 'Sheet1'.B3 >
  <
  Cell 'Sheet1'.C3 >

  #iterating columns
for col in ws.iter_cols(min_row = 2, max_col = 3, max_row = 3):
  for cell in col:
  print(cell) <
  Cell 'Sheet1'.A2 >
  <
  Cell 'Sheet1'.A3 >
  <
  Cell 'Sheet1'.B2 >
  <
  Cell 'Sheet1'.B3 >
  <
  Cell 'Sheet1'.C2 >
  <
  Cell 'Sheet1'.C3 >

To get all the rows of the worksheet we use the method worksheet.rows and to get all the columns of the worksheet we use the method worksheet.columns. Similarly, to iterate only through the values we use the method worksheet.values.


Example:

for row in ws.values:
  for value in row:
  print(value)

 

WRITING DATA TO AN EXCEL FILE

Writing to a workbook can be done in many ways such as adding a formula, adding charts, images, updating cell values, inserting rows and columns, etc… We will discuss each of these with an example.

 

CREATING AND SAVING A NEW WORKBOOK

 

#creates a new workbook
wb = openpyxl.Workbook()

#saving the workbook
wb.save("new.xlsx")

 

ADDING AND REMOVING SHEETS

 

#creating a new sheet
ws1 = wb.create_sheet(title = "sheet 2")

#creating a new sheet at index 0
ws2 = wb.create_sheet(index = 0, title = "sheet 0")

#checking the sheet names
wb.sheetnames['sheet 0', 'Sheet', 'sheet 2']

#deleting a sheet
del wb['sheet 0']

#checking sheetnames
wb.sheetnames['Sheet', 'sheet 2']

 

ADDING CELL VALUES

 

#checking the sheet value
ws['B2'].value
null

#adding value to cell
ws['B2'] = 367

#checking value
ws['B2'].value
367

 

ADDING FORMULAS

 

We often require formulas to be included in our Excel datasheet. We can easily add formulas using the Openpyxl module just like you add values to a cell.
 

For example:

import openpyxl
from openpyxl
import Workbook

wb = openpyxl.load_workbook("new1.xlsx")
ws = wb['Sheet']

ws['A9'] = '=SUM(A2:A8)'

wb.save("new2.xlsx")

The above program will add the formula (=SUM(A2:A8)) in cell A9. The result will be as below.

image

 

MERGE/UNMERGE CELLS

Two or more cells can be merged to a rectangular area using the method merge_cells(), and similarly, they can be unmerged using the method unmerge_cells().

For example:
Merge cells

#merge cells B2 to C9
ws.merge_cells('B2:C9')
ws['B2'] = "Merged cells"

Adding the above code to the previous example will merge cells as below.

image

UNMERGE CELLS

 

#unmerge cells B2 to C9
ws.unmerge_cells('B2:C9')

The above code will unmerge cells from B2 to C9.

INSERTING AN IMAGE

To insert an image we import the image function from the module openpyxl.drawing.image. We then load our image and add it to the cell as shown in the below example.

Example:

import openpyxl
from openpyxl
import Workbook
from openpyxl.drawing.image
import Image

wb = openpyxl.load_workbook("new1.xlsx")
ws = wb['Sheet']
#loading the image(should be in same folder)
img = Image('logo.png')
ws['A1'] = "Adding image"
#adjusting size
img.height = 130
img.width = 200
#adding img to cell A3

ws.add_image(img, 'A3')

wb.save("new2.xlsx")

Result:

image

CREATING CHARTS

Charts are essential to show a visualization of data. We can create charts from Excel data using the Openpyxl module chart. Different forms of charts such as line charts, bar charts, 3D line charts, etc., can be created. We need to create a reference that contains the data to be used for the chart, which is nothing but a selection of cells (rows and columns). I am using sample data to create a 3D bar chart in the below example:

Example

import openpyxl
from openpyxl
import Workbook
from openpyxl.chart
import BarChart3D, Reference, series

wb = openpyxl.load_workbook("example.xlsx")
ws = wb.active

values = Reference(ws, min_col = 3, min_row = 2, max_col = 3, max_row = 40)
chart = BarChart3D()
chart.add_data(values)
ws.add_chart(chart, "E3")
wb.save("MyChart.xlsx")

Result
image


How to Automate Excel with Python with Video Tutorial

Welcome to another video! In this video, We will cover how we can use python to automate Excel. I'll be going over everything from creating workbooks to accessing individual cells and stylizing cells. There is a ton of things that you can do with Excel but I'll just be covering the core/base things in OpenPyXl.

⭐️ Timestamps ⭐️
00:00 | Introduction
02:14 | Installing openpyxl
03:19 | Testing Installation
04:25 | Loading an Existing Workbook
06:46 | Accessing Worksheets
07:37 | Accessing Cell Values
08:58 | Saving Workbooks
09:52 | Creating, Listing and Changing Sheets
11:50 | Creating a New Workbook
12:39 | Adding/Appending Rows
14:26 | Accessing Multiple Cells
20:46 | Merging Cells
22:27 | Inserting and Deleting Rows
23:35 | Inserting and Deleting Columns
24:48 | Copying and Moving Cells
26:06 | Practical Example, Formulas & Cell Styling

📄 Resources 📄
OpenPyXL Docs: https://openpyxl.readthedocs.io/en/stable/ 
Code Written in This Tutorial: https://github.com/techwithtim/ExcelPythonTutorial 
Subscribe: https://www.youtube.com/c/TechWithTim/featured 

#python 

Best of Crypto

Best of Crypto

1657182720

How to Create Transaction with Web3.js

Abstract

The demo code provides developers with an overview of how to sign, send, and receive receipt of transactions, and verify the results of their execution. The sample also provides the event monitoring code so that the developer can understand how to listen to an event one or more times.

Getting Started

Understanding The Functions of the Smart Contract

  • Constructor: The constructor function of the smart contract, which is called when the contract is deploying, at the same time it will initialize the number to _initialNumber;
  • increment: The function of incrementing the number by given _value;
  • rest: The function of resetting the number to 0;
  • getNumber: The function of getting the number.

How to run it

  1. Install dependencies: npm install
  2. Copy the configuration file: cp .env.example .env
  3. Edit the configuration file: vim .env, copy your project ID and private key to the .env file.
PRIVATE_KEY=YOUR_PRIVATE_KEY
INFURA_ID=YOUR_PROJECT_ID

4.   Run the index.js file: node index.js

Interpret Source Code

compile.js

You can't use .sol files directly, you need to compile it to binary file firstly.

Load the smart contract file Incrementer.sol into source variable.

// Load contract
const source = fs.readFileSync("Incrementer.sol", "utf8");

Compile the smart contract file

const input = {
  language: "Solidity",
  sources: {
    "Incrementer.sol": {
      content: source,
    },
  },
  settings: {
    outputSelection: {
      "*": {
        "*": ["*"],
      },
    },
  },
};

const tempFile = JSON.parse(solc.compile(JSON.stringify(input)));

| Note: The version of solidity in this task is 0.8.0, different versions may have different compile ways.

Get the Contract Object

const contractFile = tempFile.contracts["Incrementer.sol"]["Incrementer"];

Export contractFile Object

If you want to use the contractFile object in other js files, you need to export it.

module.exports = contractFile;

index.js

1. Load the Incrementer smart contract from compile file

const contractOfIncrementer = require("./compile");

2. Read private key from environment variables

For security’s sake, the private key is not hard-coded, but it can be read as environment variables. When run this task, the dotenv plugin will automatically read the configurations in the .env file and load them as environment variables, and then you can use the private key and other environment variables via process.env.

require("dotenv").config();
const privatekey = process.env.PRIVATE_KEY;

3. Create the web3 instance

web3 is the main API of the web3js library. It is used to interact with the blockchain.

// Provider
const providerRPC = {
  development: "https://kovan.infura.io/v3/" + process.env.INFURA_ID,
  moonbase: "https://rpc.testnet.moonbeam.network",
};
const web3 = new Web3(providerRPC.development); //Change to correct network

| Note: The INFURA_ID is the PROJECT ID of the Infura project you created in last task

4. Get the account address

On blockchain, each user has a address, which is unique for others, and you can get the address by the private key. In this task, you can use the we3.eth.accounts.privateKeyToAccount API to get your account address by passing the private key as a parameter.

const account = web3.eth.accounts.privateKeyToAccount(privatekey);
const account_from = {
  privateKey: privatekey,
  accountAddress: account.address,
};

5. Get the bytecode and abi

When deploying the smart contract, you need to specify the bytecode and abi of the smart contract. The bytecode is the compiled binary code of the smart contract, and the abi (Application Binary Interface) is the interface of the smart contract.

const bytecode = contractOfIncrementer.evm.bytecode.object;
const abi = contractOfIncrementer.abi;

6. Get contract instance

In the last step, you got the bytecode and abi, so you can create the contract instance by the abi.

// Create contract instance
  const deployContract = new web3.eth.Contract(abi);

7. Create the transaction of the deployContract

// Create Tx
const deployTx = deployContract.deploy({
    data: bytecode,
    arguments: [5],
});

8. Sign the transaction

Use your private key to sign the transaction.

// Sign Tx
const deployTransaction = await web3.eth.accounts.signTransaction(
    {
        data: deployTx.encodeABI(),
        gas: 8000000,
    },
    account_from.privateKey
);

9. Send the transaction / Deploy your smart contract

Send your deploy transaction to the blockchain. You will receive a receipt, and get this contract address from the receipt.

const deployReceipt = await web3.eth.sendSignedTransaction(
    deployTransaction.rawTransaction
);
console.log(`Contract deployed at address: ${deployReceipt.contractAddress}`);

10. Load the contract instance from blockchain through above address

In previous steps, you built a contract instance, and then deployed the transaction by sending the contract to the blockchain so that you can operate the transaction later. Besides, you could also load a contract instance that is already on the blockchain so that you can operate it directly and avoid the process of deploying it.

let incrementer = new web3.eth.Contract(abi, deployReceipt.contractAddress);

11. Use the view function of a contract

Whether a contract instance is create by deploying, or by loading, you can interact with the contract once you have an instance of the contract already on the blockchain.
There are two types of contract functions: view and without view. The functions with view promise not to modify the state, while the functions without view will generate the corresponding block data on the blockchain. For example, after calling the getNumber function of the contract, you will get the public variable number of the contract, and this operation will not charge any gas.

let number = await incrementer.methods.getNumber().call();

12. Build a transaction

Before you send the transaction to the blockchain, you need to build the transaction, it means you need to specify the parameters of the transaction.

let incrementTx = incrementer.methods.increment(_value);

// Sign with Pk
let incrementTransaction = await web3.eth.accounts.signTransaction(
  {
    to: createReceipt.contractAddress,
    data: incrementTx.encodeABI(),
    gas: 8000000,
  },
  account_from.privateKey
);

13. Send the transaction and get the receipt

You can use sendSignedTransaction function to send above transaction to the blockchain, and got the receipt to check result.

const incrementReceipt = await web3.eth.sendSignedTransaction(
  incrementTransaction.rawTransaction
);

Listen to Event Increment

When invoking the interfaces of the contract, the only way to get information about the processing details, apart from the results returned by the interface, is through events.
In the interfaces, you retrieve the corresponding internal information by triggering an event, and then capturing this event generated by the external block.

const web3Socket = new Web3(
new Web3.providers.WebsocketProvider(
    'wss://kovan.infura.io/ws/v3/' + process.env.INFURA_ID
));
incrementer = new web3Socket.eth.Contract(abi, createReceipt.contractAddress);

| kovan don't support http protocol to event listen, need to use websocket. More details , please refer to this blog

Listen to Increment event only once

incrementer.once('Increment', (error, event) => {
    console.log('I am a onetime event listener, I am going to die now');
});

Listen to Increment event continuously

incrementer.events.Increment(() => {
    console.log("I am a longlive event listener, I get a event now");
});

References

Link: https://github.com/Dapp-Learning-DAO/Dapp-Learning/tree/main/basic/02-web3js-transaction

#solidity #blockchain #smartcontract #web3 #web3.js

How to Create Transaction with Web3.js

Abstract

The demo code provides developers with an overview of how to sign, send, and receive receipt of transactions, and verify the results of their execution. The sample also provides the event monitoring code so that the developer can understand how to listen to an event one or more times.

Getting Started

Understanding The Functions of the Smart Contract

  • Constructor: The constructor function of the smart contract, which is called when the contract is deploying, at the same time it will initialize the number to _initialNumber;
  • increment: The function of incrementing the number by given _value;
  • rest: The function of resetting the number to 0;
  • getNumber: The function of getting the number.

How to run it

  1. Install dependencies: npm install
  2. Copy the configuration file: cp .env.example .env
  3. Edit the configuration file: vim .env, copy your project ID and private key to the .env file.
PRIVATE_KEY=YOUR_PRIVATE_KEY
INFURA_ID=YOUR_PROJECT_ID

4.   Run the index.js file: node index.js

Interpret Source Code

compile.js

You can't use .sol files directly, you need to compile it to binary file firstly.

Load the smart contract file Incrementer.sol into source variable.

// Load contract
const source = fs.readFileSync("Incrementer.sol", "utf8");

Compile the smart contract file

const input = {
  language: "Solidity",
  sources: {
    "Incrementer.sol": {
      content: source,
    },
  },
  settings: {
    outputSelection: {
      "*": {
        "*": ["*"],
      },
    },
  },
};

const tempFile = JSON.parse(solc.compile(JSON.stringify(input)));

| Note: The version of solidity in this task is 0.8.0, different versions may have different compile ways.

Get the Contract Object

const contractFile = tempFile.contracts["Incrementer.sol"]["Incrementer"];

Export contractFile Object

If you want to use the contractFile object in other js files, you need to export it.

module.exports = contractFile;

index.js

1. Load the Incrementer smart contract from compile file

const contractOfIncrementer = require("./compile");

2. Read private key from environment variables

For security’s sake, the private key is not hard-coded, but it can be read as environment variables. When run this task, the dotenv plugin will automatically read the configurations in the .env file and load them as environment variables, and then you can use the private key and other environment variables via process.env.

require("dotenv").config();
const privatekey = process.env.PRIVATE_KEY;

3. Create the web3 instance

web3 is the main API of the web3js library. It is used to interact with the blockchain.

// Provider
const providerRPC = {
  development: "https://kovan.infura.io/v3/" + process.env.INFURA_ID,
  moonbase: "https://rpc.testnet.moonbeam.network",
};
const web3 = new Web3(providerRPC.development); //Change to correct network

| Note: The INFURA_ID is the PROJECT ID of the Infura project you created in last task

4. Get the account address

On blockchain, each user has a address, which is unique for others, and you can get the address by the private key. In this task, you can use the we3.eth.accounts.privateKeyToAccount API to get your account address by passing the private key as a parameter.

const account = web3.eth.accounts.privateKeyToAccount(privatekey);
const account_from = {
  privateKey: privatekey,
  accountAddress: account.address,
};

5. Get the bytecode and abi

When deploying the smart contract, you need to specify the bytecode and abi of the smart contract. The bytecode is the compiled binary code of the smart contract, and the abi (Application Binary Interface) is the interface of the smart contract.

const bytecode = contractOfIncrementer.evm.bytecode.object;
const abi = contractOfIncrementer.abi;

6. Get contract instance

In the last step, you got the bytecode and abi, so you can create the contract instance by the abi.

// Create contract instance
  const deployContract = new web3.eth.Contract(abi);

7. Create the transaction of the deployContract

// Create Tx
const deployTx = deployContract.deploy({
    data: bytecode,
    arguments: [5],
});

8. Sign the transaction

Use your private key to sign the transaction.

// Sign Tx
const deployTransaction = await web3.eth.accounts.signTransaction(
    {
        data: deployTx.encodeABI(),
        gas: 8000000,
    },
    account_from.privateKey
);

9. Send the transaction / Deploy your smart contract

Send your deploy transaction to the blockchain. You will receive a receipt, and get this contract address from the receipt.

const deployReceipt = await web3.eth.sendSignedTransaction(
    deployTransaction.rawTransaction
);
console.log(`Contract deployed at address: ${deployReceipt.contractAddress}`);

10. Load the contract instance from blockchain through above address

In previous steps, you built a contract instance, and then deployed the transaction by sending the contract to the blockchain so that you can operate the transaction later. Besides, you could also load a contract instance that is already on the blockchain so that you can operate it directly and avoid the process of deploying it.

let incrementer = new web3.eth.Contract(abi, deployReceipt.contractAddress);

11. Use the view function of a contract

Whether a contract instance is create by deploying, or by loading, you can interact with the contract once you have an instance of the contract already on the blockchain.
There are two types of contract functions: view and without view. The functions with view promise not to modify the state, while the functions without view will generate the corresponding block data on the blockchain. For example, after calling the getNumber function of the contract, you will get the public variable number of the contract, and this operation will not charge any gas.

let number = await incrementer.methods.getNumber().call();

12. Build a transaction

Before you send the transaction to the blockchain, you need to build the transaction, it means you need to specify the parameters of the transaction.

let incrementTx = incrementer.methods.increment(_value);

// Sign with Pk
let incrementTransaction = await web3.eth.accounts.signTransaction(
  {
    to: createReceipt.contractAddress,
    data: incrementTx.encodeABI(),
    gas: 8000000,
  },
  account_from.privateKey
);

13. Send the transaction and get the receipt

You can use sendSignedTransaction function to send above transaction to the blockchain, and got the receipt to check result.

const incrementReceipt = await web3.eth.sendSignedTransaction(
  incrementTransaction.rawTransaction
);

Listen to Event Increment

When invoking the interfaces of the contract, the only way to get information about the processing details, apart from the results returned by the interface, is through events.
In the interfaces, you retrieve the corresponding internal information by triggering an event, and then capturing this event generated by the external block.

const web3Socket = new Web3(
new Web3.providers.WebsocketProvider(
    'wss://kovan.infura.io/ws/v3/' + process.env.INFURA_ID
));
incrementer = new web3Socket.eth.Contract(abi, createReceipt.contractAddress);

| kovan don't support http protocol to event listen, need to use websocket. More details , please refer to this blog

Listen to Increment event only once

incrementer.once('Increment', (error, event) => {
    console.log('I am a onetime event listener, I am going to die now');
});

Listen to Increment event continuously

incrementer.events.Increment(() => {
    console.log("I am a longlive event listener, I get a event now");
});

References

Link: https://github.com/Dapp-Learning-DAO/Dapp-Learning/tree/main/basic/02-web3js-transaction

#solidity #blockchain #smartcontract #web3 #web3.js